Thursday, June 14, 2007

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 which I have modified, 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)

3 comments:

Anonymous said...

meh

Peter said...

declare @x varchar(4000)
set @x = ' '
if nullif(@x,'') is null
print 'null or empty'
else
print 'not null or empty'

Peter said...

Also, if you're going to write a function, it is better to use so many lines on empty syntax:

CREATE FUNCTION dbo.IsNotNullOrEmpty(@text NVARCHAR(4000),@BlanksIsEmpty bit)
RETURNS BIT
AS BEGIN

DECLARE @ReturnValue bit

IF @BlanksIsEmpty = 0
SET @ReturnValue= SIGN(COALESCE(DATALENGTH(@text),0))

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

RETURN @ReturnValue
END
Go