I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Sql Server: IsNullOrEmpty function

A coworker who works mainly in C# wanted to know if there's an IsNullOrEmpty function.

After a brief NO, i've given him this one.

So far it works great.

 

CREATE FUNCTION dbo.IsNullOrEmpty(@text NVARCHAR(4000))
RETURNS BIT
AS
BEGIN
    IF ISNULL(@text, '') = ''
    BEGIN
        RETURN 1
    END
    
    RETURN 0
END

GO

DECLARE @text VARCHAR(100)

SELECT @text = 'gdrfash5'
SELECT dbo.IsNullOrEmpty(@text)

SELECT @text = ''
SELECT dbo.IsNullOrEmpty(@text)

SELECT @text = '   '
SELECT dbo.IsNullOrEmpty(@text)

SELECT @text = null
SELECT dbo.IsNullOrEmpty(@text)

 

kick it on DotNetKicks.com

Print | posted on Wednesday, June 13, 2007 3:21 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Sql Server: IsNullOrEmpty function

This may be a cultural difference, but your third test should return false. Three ASCII 32 characters is not the same as nothing.
6/13/2007 4:10 PM | Bill Curnow
Gravatar

# re: Sql Server: IsNullOrEmpty function

could be A cultural diff :)
i think it should return true because it holds no relevant data.

but if you don't want to you can easily add a replace before the comparison
6/13/2007 4:35 PM | Mladen
Gravatar

# re: Sql Server: IsNullOrEmpty function

"Relevant" to the user, the programmer, or the computer? :)

This is really an artifact of garbage-in/garbage-out. If leading or trailing spaces are not relevant to the application, they should be trimmed before the data is inserted into the database. Of course, I'm probably preaching to the choir.
6/13/2007 5:44 PM | Bill Curnow
Gravatar

# re: Sql Server: IsNullOrEmpty function

relevant to my current needs :)

i agree completly with you. you are preaching to the choir :))))
6/13/2007 10:40 PM | Mladen
Gravatar

# re: Sql Server: IsNullOrEmpty function

CREATE FUNCTION dbo.IsNullOrEmpty(@text VARCHAR(4000))
RETURNS BIT
AS

BEGIN
DECLARE @Return BIT

IF @text IS NULL
BEGIN
SET @Return = 1 --true
GOTO RET
END

IF DATALENGTH(@text)= DATALENGTH('') BEGIN
SET @Return = 1 --true
GOTO RET
END

RETURN 0 --false

RET:
RETURN @Return
END
GO
6/14/2007 2:04 PM | michael
Gravatar

# re: Sql Server: IsNullOrEmpty function

IMO "DATALENGTH" is the way to go. :-)
6/14/2007 2:33 PM | Tom
Gravatar

# re: Sql Server: IsNullOrEmpty function

Toooo much code which can be handled in 1 line by using the SIGN function

ALTER FUNCTION dbo.IsNullOrEmpty(@text NVARCHAR(4000))
RETURNS BIT
AS

BEGIN
RETURN SIGN(COALESCE(DATALENGTH(@text),0))
--Use this if you want blanks to return 0
--RETURN SIGN(COALESCE(DATALENGTH(RTRIM(@text)),0))
END
Go

SELECT dbo.IsNullOrEmpty(null),dbo.IsNullOrEmpty('azas'),dbo.IsNullOrEmpty(' '),dbo.IsNullOrEmpty('')

depending on what ' ' should return use the first or the second line

Denis
6/14/2007 4:43 PM | Denis the SQL Menace
Gravatar

# re: Sql Server: IsNullOrEmpty function

very nice!
Thanx Denis.
6/14/2007 5:17 PM | Mladen
Gravatar

# re: Sql Server: IsNullOrEmpty function

And then your coworker will happily run
select * from sometablewithmillionrows
wherre dbo.IsNullOrEmpty(somefield)=0

killing CPU on SQL Server....
Good luck
6/14/2007 6:02 PM | Alexander
Gravatar

# re: Sql Server: IsNullOrEmpty function

well... if we had a table with million rows then i'd be worried. :)

actually all we needed this function for was to help transform some data.
6/14/2007 6:03 PM | Mladen
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET