String.IsNullOrEmpty like function for VARCHARs in

问题:

Say I've got a function or stored procedure that takes in several VARCHAR parameters. I've gotten tired of writing SQL like this to test if these parameters have a value:

IF @SomeVarcharParm IS NOT NULL AND LEN(@SomeVarcharParm) > 0
BEGIN
    -- do stuff
END

There's gotta be a better way to do this. Isn't there?

回答1:

You can do ISNULL(@SomeVarcharParam, '') <> '' or you can create a UDF that returns a bit:

create function dbo.IsNullOrEmpty(@x varchar(max)) returns bit as
BEGIN
IF @SomeVarcharParm IS NOT NULL AND LEN(@SomeVarcharParm) > 0
    RETURN 0
ELSE
    RETURN 1
END

And call that using IF NOT dbo.IsNullOrEmpty(@SomeVarcharParam) BEGIN ...

Keep in mind that when calling a UDF, you MUST prefix the owner of it (here, dbo.)

回答2:

IF COALESCE(@SomeVarcharParm, '') <> ''
BEGIN
   -- do stuff
END

回答3:

If I'm concatenating or coalescing a string inline (within a select statement), and I want to check if the column is NULL or Empty, I do this:

ISNULL('starting to build string ' 
+ NULLIF(some_table..some_col_that_might_be_null_or_empty, '')
, 'string to append if the resulting concatenation is null')

The NULLIF on the inner part of the expression will force the column to be NULL if it's empty, then the outer ISNULL expression can depend on consistent input and react accordingly.

回答4:

Here is my function that "extends" ISNULL and checks for empty as well. The test value is checked for null and if it is not null it is trimmed and then checked for length.

The function returns the test string if it is NOT Null or Empty, otherwise the second string is returned.

CREATE FUNCTION [dbo].[ISNULLOREMPTY]
(   
    @value NVARCHAR(max),
    @return NVARCHAR(max)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN 

IF (@value IS NULL)
BEGIN
    RETURN @return
END
ELSE
BEGIN
    IF (LEN(LTRIM(@value)) = 0)
    BEGIN 
        RETURN @return
    END 
END

RETURN @value;
END
GO

回答5:

I realize this is an old question, but this is what I use in MSSQL: LEN(ISNULL(@asdf, ''))>0

Example:

DECLARE @asdf varchar(10)
SET @asdf = NULL --You can change this value to test different outputs

BEGIN IF LEN(ISNULL(@asdf, '')) > 0
   PRINT @asdf
ELSE
   PRINT 'IS NullOrEmpty'
END

--You can use it inline like this:
PRINT CASE LEN(ISNULL(@asdf, '')) WHEN 0 THEN 'IS NullOrEmpty' ELSE @asdf END

I think this is simpler and more straight forward than the other solutions because it is literally checking if the string is null or has a length of 0.

回答6:

You don't need to check for null before calling LEN. You can just use LEN(@SomeVarcharParm) > 0. This will return false if the value is NULL, '', or ' '. This is because NULL > 0 returns false. See for yourself run:

SELECT 
 CASE WHEN NULL > 0 THEN 'NULL > 0 = true' ELSE 'NULL > 0 = false' END,
 CASE WHEN LEN(NULL) > 0 THEN 'LEN(NULL) = true' ELSE 'LEN(NULL) = false' END,
 CASE WHEN LEN('') > 0 THEN 'LEN('''') > 0 = true' ELSE 'LEN('''') > 0 = false' END,
 CASE WHEN LEN(' ') > 0 THEN 'LEN('' '') > 0 = true' ELSE 'LEN('' '') > 0 = false' END,
 CASE WHEN LEN(' test ') > 0 THEN 'LEN('' test '') > 0 = true' ELSE 'LEN('' test '') > 0 = false' END

回答7:

You can just do IF @SomeVarcharParam <> '' since the condition will evaluate to NULL and the branch won't be taken if the parameter is null

回答8:

I upvoted Atron's answer though he technically implemented IfNullOrWhiteSpace.

Here's my implementation of IfNullOrEmpty():

IF EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[IfNullOrEmpty]' ) and type in ( N'FN'))
    DROP FUNCTION dbo.IfNullOrEmpty
go

CREATE FUNCTION dbo.IfNullOrEmpty(@value varchar(max), @substitute varchar(max)) returns varchar(max) as
BEGIN
    IF @value IS NOT NULL AND LEN(@value) > 0
        RETURN @value
    RETURN @substitute  
END

回答9:

Use this function (based on Derek's):

CREATE FUNCTION dbo.isNullOrEmpty(@x varchar(max)) RETURNS BIT AS
BEGIN
    IF @x IS NOT NULL AND LEN(@x) > 0
        RETURN 0

    RETURN 1
END

as

dbo.isNullOrEmpty(@someVar)

or

WHERE dbo.isNullOrEmpty(@someVar) = 1

in a stored procedure or query.