THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

C# IsNullOrEmpty Function In SQL Server

Mladen Prajdic has created a SQL equivalent of the C# IsNotNullOrEmpty
 I looked at it and thought that there was way too much code

Here is my version, you pass an additional parameter in to indicate whether you want blanks only to count or not

 CREATE FUNCTION dbo.IsNotNullOrEmpty(@text NVARCHAR(4000),@BlanksIsEmpty bit)





DECLARE @ReturnValue bit


IF @BlanksIsEmpty = 0


            SELECT @ReturnValue= SIGN(COALESCE(DATALENGTH(@text),0))




            SELECT @ReturnValue= SIGN(COALESCE(DATALENGTH(RTRIM(@text)),0))



RETURN @ReturnValue



Here are some calls where we want blanks to return as empty or null
The function returns = if it is empty and 1 if it is not empty


SELECT dbo.IsNotNullOrEmpty(null,1),dbo.IsNotNullOrEmpty('azas',1),

dbo.IsNotNullOrEmpty(' ',1),dbo.IsNotNullOrEmpty('',1)

Here are some calls where we don't want blanks to return as empty or null

SELECT dbo.IsNotNullOrEmpty(null,0),dbo.IsNotNullOrEmpty('azas',0),

dbo.IsNotNullOrEmpty(' ',0),dbo.IsNotNullOrEmpty('',0)

My function is the opposite of Mladen's I check for is NOT null or empty instead of IS null or empty (easier to code it with the SIGN function)

Published Thursday, June 14, 2007 10:57 AM by Denis Gobo



Mladen said:


I just hope that this doesn't start some kind of an avalanche like the Reverse function in c# did a while ago :))

June 14, 2007 10:15 AM

Denis Gobo said:

SIGN is one of those function which you almost never see used in code. Sometimes you are asked by the

February 12, 2008 2:58 PM

Sam said:

Great example. I think you can also use XOR '^' to turn it back to IsNullOrEmpty:

 SELECT @ReturnValue = (1 ^ SIGN(COALESCE(DATALENGTH(@text),0)))


September 5, 2009 12:42 PM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement