SQL Training Online

  • Training Courses
  • Beginner Book
  • Blog

November 5, 2012 by Joey Blue Leave a Comment

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.
[sharebox4 sharetext=”Share This Page”] [/sharebox4]

How do you filter a SQL Null or Empty String?

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
SQL Null or Empty String Results

 

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

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

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

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,'') = ''
SQL Isnull Results

 

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: https://www.sqltrainingonline.com

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

Google+: https://plus.google.com/#100925239624117719658/posts

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

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

Filed Under: SQL Training Tagged With: SQL Course, SQL Empty String, SQL IS Null, SQL ISNULL Function, SQL Null, SQL Training Online, SQL Tutorial, SQL Where

November 1, 2012 by Joey Blue Leave a Comment

How to Test SQL Server Functions

Video: How to Test SQL Server Functions – SQL Training Online
In this video, I show you a trick on how to test SQL Functions in SQL Server 2012.
[sharebox4 sharetext=”Share This Page”] [/sharebox4]

How do you test a SQL Server Function?

Testing a new function is easier to do when you don’t actually try to query a table to do it.

I will show you how to trim off the front part of an email address by first testing the functions, then putting them into the actual SQL query.

How To Test SQL Server Functions

Let’s start with the following SQL query that is written against the Adventure Works database.

SELECT
s.[BusinessEntityID]
,s.[Name]
,ct.[Name] AS [ContactType]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,pp.[PhoneNumber]
,pnt.[Name] AS [PhoneNumberType]
,ea.[EmailAddress]
,p.[EmailPromotion]
FROM [Sales].[Store] s
INNER JOIN [Person].[BusinessEntityContact] bec
ON bec.[BusinessEntityID] = s.[BusinessEntityID]
INNER JOIN [Person].[ContactType] ct
ON ct.[ContactTypeID] = bec.[ContactTypeID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = bec.[PersonID]
LEFT OUTER JOIN [Person].[EmailAddress] ea
ON ea.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];

This query joins multiple tables and can be intimidating to the beginner. The main thing to look at is that it returns an email address.

I want to trim off the front part of the email address.

So, I will copy an email address to work on as an example.

‘[email protected]’

Now, SQL Server allows you to run a Select query without a FROM clause.

We will use that capability to test our function. Here is an example.

select '[email protected]'

Now, I want to find the ‘@’ character in the string, so I will use the CharIndex function. Now it is time to test the SQL Server Function:

select charindex('@','[email protected]')

This returns the number 9, since the ‘@’ sign is the 9th character.

Now, we need to use the substring to pull off all of the character until the ‘@’ sign. So, let’s test the substring function:

select charindex('@','[email protected]')
,substring('[email protected]',1,9-1)

This does what we want, so now we need to test both SQL Server Functions together.

select substring('[email protected]',1,charindex('@','[email protected]')-1)

This is exactly what we want. So, we just need to put the function into the main query.

SELECT
s.[BusinessEntityID]
,s.[Name]
,ct.[Name] AS [ContactType]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,pp.[PhoneNumber]
,pnt.[Name] AS [PhoneNumberType]
,ea.[EmailAddress]
,substring(ea.[EmailAddress] ,1,charindex('@',ea.[EmailAddress] )-1)
,p.[EmailPromotion]
FROM [Sales].[Store] s
INNER JOIN [Person].[BusinessEntityContact] bec
ON bec.[BusinessEntityID] = s.[BusinessEntityID]
INNER JOIN [Person].[ContactType] ct
ON ct.[ContactTypeID] = bec.[ContactTypeID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = bec.[PersonID]
LEFT OUTER JOIN [Person].[EmailAddress] ea
ON ea.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.[BusinessEntityID] = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID];

There it is. That is how you Test SQL Server Functions.

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: https://www.sqltrainingonline.com

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

Google+: https://plus.google.com/#100925239624117719658/posts

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

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

Filed Under: SQL Training Tagged With: SQL Charindex, SQL Function, SQL Substring, SQL Tip, SQL Training Online, SQL Video

Recent Posts

  • SQL Database Normalization – 1NF, 2NF, 3NF, and 4NF
  • SQL Joins Tutorial for Beginners – Inner Join, Left Join, Right Join, Full Outer Join – SQL Training Online
  • Zillow House Price Analysis from CSV Archive – #PowerBI 002
  • Learn Basic SQL – 1 Hour Training Course – SQL Training Online
  • Create Table Statement in SQL Server and Inserting Baseball Homerun Leader Dataset – SQL Training Online

Popular Posts

  • SQL Functions
  • SQL Jobs in Oracle and Microsoft SQL Server
  • Troubleshooting a SQL Join
Copyright © 2023 · SQLTrainingOnline.com · Consulting at EmbarkBlue.com