IsEmpty function like ISNULL in SQL Server?

Question

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.

Answer 1

Try NULLIF as below

NULLIF(@SKU,'')

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

Try below in select clause.

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

Below in Where Clause.

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

Answer 6

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

Answer 7

if you consider ' ' as empty string you can use

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

Answer 8

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

Answer 9

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