CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 33974

IsEmpty function like ISNULL in SQL Server?

I have this sql,

IF(@ID = '')
BEGIN
    SET @ID = NULL;
END

IF(@Name = '')
BEGIN
    SET @Name = NULL;
END

IF(@PhoneNumber = '')
BEGIN
    SET @PhoneNumber = NULL;     
END

IF(@Price = '')
BEGIN
    SET @Price = NULL;
END

IF(@NewPrice = '')
BEGIN
    SET @NewPrice = NULL;
END

IF(@ModelNumber = '')
BEGIN
    SET @ModelNumber = NULL;
END

IF(@SKU = '')
BEGIN
    SET @SKU = NULL;
END

I am looking IsEmpty function like ISNULL. So that I can,

ISEMPTY(@SKU, NULL)

Is this is possible in SQL.

Upvotes: 22

Views: 110506

Answers (9)

Martin Smith
Martin Smith

Reputation: 453287

Personally I don't like the nested NULLIF and ISNULL in some of the other answers. I find this logic difficult to follow and prefer using a simple IIF( condition, true_value, else_value )

SQL uses three valued logic true, false and unknown.

With IIF if the condition evaluates to true you go through to the first case, otherwise (when it is false or unknown) you go through to the else case.

Comparisons with NULL (whether = or <> or LIKE or NOT LIKE) will evaluate as unknown.

So you just have to take care to think about the semantics you want and express it in a manner that empty string will be either bucketed with or without NULL as desired.

One other thing to be aware of is that SQL Server usually ignores trailing spaces for LEN or string comparisons. It does treat them as significant in the value on the LHS of LIKE however.

So the following expressions provide a variety of different semantics to choose the desired one from.

SELECT 
  ValueUnderTest
 ,StringLength = DATALENGTH(ValueUnderTest)
 ,IsEmpty = IIF(ValueUnderTest = '', 'True', 'False') 
 ,IsNullOrEmpty = IIF(ValueUnderTest <> '', 'False', 'True')
 ,IsZeroLength = IIF('' LIKE ValueUnderTest, 'True', 'False')
 ,IsNullOrZeroLength = IIF('' NOT LIKE ValueUnderTest, 'False', 'True')
 FROM
 (
 VALUES (''),  
        (' '), 
        (NULL), 
        ('Fish')
 ) TestData(ValueUnderTest)

Returns

ValueUnderTest StringLength IsEmpty IsNullOrEmpty IsZeroLength IsNullOrZeroLength
0 True True True True
1 True True False False
NULL NULL False True False True
Fish 4 False False False False

Upvotes: 2

user6186047
user6186047

Reputation: 1

When what you want is a boolean result e.g. Y/N or true/false such as in a report, then such suggested simple statements such as NULLIF will not help. Here is a solution:

case when LEN(isnull(iv.value2, 'Y')) = 0 then 'Y' else 'N' end isEmpty

Upvotes: 0

Juan Irigoyen
Juan Irigoyen

Reputation: 1

if you consider ' ' as empty string you can use

LEN(RTRIM(ISNULL(FieldName,''))) > 0

Upvotes: 0

ramit girdhar
ramit girdhar

Reputation: 2412

Try below in select clause.

select ISNULL( nullif(EmptyOrNullOrBlankColumn,'' ),NULL)

Below in Where Clause.

where ISNULL( nullif(EmptyOrNullOrBlankColumn,'' ),NULL) is not null

Upvotes: 2

user6123020
user6123020

Reputation: 1

SELECT ISNULL( CASE StringColum1  WHEN '' THEN NULL ELSE textcolum1 END ,textcolum2)

Upvotes: -1

Robert
Robert

Reputation: 25753

Try NULLIF as below

NULLIF(@SKU,'')

Upvotes: 68

Mentatmatt
Mentatmatt

Reputation: 525

Use SET @SKU = NULLIF(@SKU,'') to set @SKU to null where @SKU equals the value of the second argument.

IsEmpty isn't a built-in T-SQL function, but NULLIF can be used to achieve a similar behavior.

Upvotes: 8

TechDo
TechDo

Reputation: 18629

Please try:

SET @YourValue=ISNULL(NULLIF(@YourValue,' '), NULL)

which returns NULL if value is NULL, empty or space.

Note: NULLIF returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a null value of the type of the first expression.

Upvotes: 12

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

This may be what you're looking for:

SET @SKU = CASE @SKU WHEN '' THEN NULL ELSE @SKU END

EDIT

For all your variables...

SELECT
  @ID = CASE @ID WHEN '' THEN NULL ELSE @ID END,
  @Name = CASE @Name WHEN '' THEN NULL ELSE @Name END,
  @PhoneNumber = CASE @PhoneNumber WHEN '' THEN NULL ELSE @PhoneNumber END,
  @Price = CASE @Price WHEN '' THEN NULL ELSE @Price END,
  @NewPrice = CASE @NewPrice WHEN '' THEN NULL ELSE @NewPrice END,
  @ModelNumber = CASE @ModelNumber WHEN '' THEN NULL ELSE @ModelNumber END,
  @SKU = CASE @SKU WHEN '' THEN NULL ELSE @SKU END¸

EDIT2

If anyone uses the kind of code I suggested, forget it and use NULLIF() as other guys suggested. I COMPLETELY FORGOT it exists.

Upvotes: 3

Related Questions