How to Filter for SQL Null or Empty String

Video: How to Filter for SQL Null or Empty String - SQL Training Online

In this video, I show you how to filter a SQL table that has both Null values and an Empty string. I use SQL Server 2012 to demonstrate the technique.

How do you filter a SQL Null or Empty String?

A null value in a database really means the lack of a value. It is a special -value- that you can-t compare to using the normal operators. You have to use a clause in SQL IS Null.

On the other hand, an empty string is an actual value that can be compared to in a database. You simply use two ticks together.

”

Let-s take a look at the vendor table to demonstrate this.

select * from vendor

In this table, I specifically put in some email addresses that are both null and empty strings.

Let-s pull out the NULL values using the IS NULL operator.

select * from vendor

where vendor_email is null

![SQL Null or Empty String Results](../../assets/blog/2012/11/image_thumb1.png)

Next, I want to pull out the empty string using the tick-tick, or empty string.

select * from vendor

where vendor_email = ''

![SQL Null or Empty String Results](../../assets/blog/2012/11/image_thumb2.png)

If you want to combine them to search for the SQL null or empty string together and retrieve all of the empty strings and nulls all at once, you could do something like this.

select * from vendor

where vendor_email = ''

or vendor_email is null

![SQL Null or Empty String Results](../../assets/blog/2012/11/image_thumb3.png)

Finally, I want to show you a little trick that I have used on occasion to change all of the null values to empty strings and then compare the result to an empty string.

select * from vendor

where isnull(vendor_email,'') = ''

And that is it.

Here is the script to create the vendor table I was using as an example.

create table dbo.vendor

(

vendor_id int identity(1,1) primary key,

vendor_name varchar(50),

vendor_email varchar(100)

)

insert into vendor (vendor_name,vendor_email) values ('Dell','[email protected]');

insert into vendor (vendor_name,vendor_email) values ('IBM','[email protected]');

insert into vendor (vendor_name,vendor_email) values ('Microsoft','[email protected]');

insert into vendor (vendor_name,vendor_email) values ('Oracle','');

insert into vendor (vendor_name,vendor_email) values ('Intel',NULL);

insert into vendor (vendor_name,vendor_email) values ('Apple','[email protected]');

There it is. That is how you filter for SQL Null or Empty String.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

If you enjoy the video, please give it a like, comment, or subscribe to my channel.

You can visit me at any of the following:

SQL Training Online: /

Twitter: http://www.twitter.com/sql_by_joey

Google+:

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline