SQL Like

The SQL Like is the clause that allows you to do wildcard searches in the SQL language.

The Like clause can only be used on string data types (char and varchar in Microsoft SQL Server).

You can use it in a SQL statement anywhere you would normally use the “=” (equal sign).

There are two wildcard characters you can use:

• % – Is a multi-character wildcard.  Which means it will match more than one character.
• _ – The underscore is a single character wildcard.  It only matches one character.

Let’s take a look at a few examples.

I want to look at a student table:

select * from student; 

Here are the results:

So the student table has 7 rows and 8 columns.

Now, lets find all of the addresses that are designated “Rd”.

I have to do this by searching with the SQL Like clause.

Here is the query to do that:

select * from student where S_ADDRESS LIKE '%Rd%'; 

The where clause says, “I want to look at the S_ADDRESS column and find everything that has any number of characters, then an Rd, then any number of characters.

Here are the results:

We get 3 records that contain the “RD”.

No what if I wanted to find all of the zip codes that had a third character of 1?

The query would look like this:

select * from student where S_ZIPCODE LIKE '__1%'; 

Let’s take a look at that LIKE string: ‘__1%’

The first part of that string is actually 2 underscores.  That is because we want exactly 2 characters, then we want to see the number 1.  (I say number, but it is actually a varchar in the database.)

Here are the results:

I have also used it in a CASE statement inside of the SELECT clause, but I will leave that for another post.

As always, if you have any suggestions for topics you would like for me to blog, or if you have any general comments, leave them below.