C# IsNullOrEmpty Function In SQL Server

Denis Gobo — Thu, 14 Jun 2007 13:57:00 GMT

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

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

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)

Denis Gobo : SQL Server 2005, Functions