THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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)

RETURNS BIT

AS

 

BEGIN

DECLARE @ReturnValue bit

 

IF @BlanksIsEmpty = 0

      BEGIN

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

      END

ELSE

      BEGIN

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

      END

 

RETURN @ReturnValue

END

Go

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mladen said:

Cool!

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)))

Sam

September 5, 2009 12:42 PM

Leave a Comment

(required) 
(required) 
Submit

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 http://sqlservercode.blogspot.com/ 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

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement