I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net

posts - 199, comments - 2249, trackbacks - 33

My Links

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 NT Conference
Welcome to my blog.

Search this Blog

Users Online:

Article Categories

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

<< C# 2.0: Arity - ever heard of it? | Home | Book Review: Inside SQL Server 2005: THE STORAGE ENGINE >>

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)

Feedback

# 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

# 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

# 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

# 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

# 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

# re: Sql Server: IsNullOrEmpty function

IMO "DATALENGTH" is the way to go. :-)

6/14/2007 2:33 PM | Tom

# 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

# re: Sql Server: IsNullOrEmpty function

I created a new one where you can pass in if you want blanks only to count as empty or not, see here: http://sqlservercode.blogspot.com/2007/06/c-isnullorempty-function-in-sql-server.html
or here: http://sqlblog.com/blogs/denis_gobo/archive/2007/06/14/1466.aspx

BTW I kicked your story ;-)

6/14/2007 5:10 PM | Denis the SQL Menace

# re: Sql Server: IsNullOrEmpty function

very nice!
Thanx Denis.

6/14/2007 5:17 PM | Mladen

# 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

# 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