Comparing String,if it is NULL in Sql Server 2008

Question

I am using the following code to compare a string if it is equal to NULL in a Stored Procedure in Sql Server 2008.

IF(Name != '')
    // Do some stuff
ELSE IF(Phone != '')
    // Do some stuff
ELSE
    // Do some other stuff

Is there any disadvantages or drawback's in using the above format of Checking a string.

I was using the below code before:

IF(Name IS NOT NULL)
    // Do some stuff
ELSE IF(Phone IS NOT NULL)
    // Do some stuff
ELSE
    // Do some other stuff

which is not working as expected.

This would imply that the data is empty string, and not null. — StuartLC
– StuartLC, Commented Feb 7, 2014 at 11:25

Thorsten Dittmar · Accepted Answer · 2014-02-07 11:26:31Z

3

!=/<> '' is not the same as IS NOT NULL! You need this:

IF(Name <> '')
    // Do some stuff
ELSE IF(Phone  <> '')
    // Do some stuff
ELSE
    // Do some other stuff

If Name or Phone can be NULL, you need this:

IF(ISNULL(Name, '') <> '')
    // Do some stuff
ELSE IF(ISNULL(Phone, '')  <> '')
    // Do some stuff
ELSE
    // Do some other stuff

In SQL, NULL is always <> ''. In fact, in most configurations, NULL is also <> NULL.

answered Feb 7, 2014 at 11:26

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thorsten Dittmar Over a year ago

What do you mean "the same stuff"? If you want to compare any field/variable in SQL to anything, you mostly need to account for the case that the field/variable can be NULL. You could do something like IF (ISNULL(datefield, CONVERT(DateTime, '20140101')) <> CONVERT(DateTime, '20140101') ....

Mirage Over a year ago

sorry, "the same stuff" means comparing the DateTime datafield if its NULL or not. Thank you!

Alex K. · Accepted Answer · 2014-02-07 11:26:48Z

0

They are different: if Name is NULL it will never be equal to, or not equal to '' because nothing is equivalent to NULL.

The only way to test for NULL is with IS NULL.

If you want to test for NULL or '' then: IF ISNULL(val, '') = ''

answered Feb 7, 2014 at 11:26

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Collectives™ on Stack Overflow

Comparing String,if it is NULL in Sql Server 2008

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related