The Basics of Like

In most cases the Like Statement is used to find values where the string contains a specific pattern. For example, if you would query the syscolumns tables to find any column where the name contains "sys". In that case the % sign is used as a wildcard.

select *
from syscolumns
where name like '%sys%'

Leaving out one of the % wildcards, you can also use it to find string values that start or end with a specific value.

-- Starts with:
select *
from syscolumns
where name like 'sys%'

-- Ends with:
select *
from syscolumns
where name like '%sys'

The other wildcard

Lesser known than the % wildcard is the _ (underscore) wildcard. It is pretty much the same as the % wildcard in that it stands for any character. But contrary to the % that stands for an unlimmited amount of characters, the _ wildcard stands for one single position.

So if you want to search for both major and minor, you could use "m__or"

select *
from syscolumns
where name like 'idm__or'

Both wildcards can also be used simultaneously:

select *
from syscolumns
where name like '%m__or'

But where did the underscore go?

Now there are some columns in syscolumns that have an underscore in their name. Let's serach for those:

select *
from syscolumns
where name like '%_%'

You can try it for yourself: it wont work and it will return all rows. This is strangely enough as expected. You are looking for any value that starts with something, then has one character and ends with something. And yup, any row will match that criteria!

So how do we look for string values that contain an underscore? Here' how you can do it:

select *
from syscolumns
where name like '%/_%' escape '/'

With this, the / sign is removed and anything behind it will be taken literaly.

More advanced with Square Brackects

You could go even more advanced using the Square Brackets. Using these you limit the wildcard search to just the characters that are within the Square Brackets.

Let's find all the columns that start with A, B or C:

select *
from syscolumns
where name like '[ABC]%'
order by name

To find all the columns which name not starts with A, B or C, just add the ^ (circumflex) symbol:

select *
from syscolumns
where name like '[^ABC]%'
order by name

Now let's go for all the columns that start with anything from A to Z. Should we type the complete alphabet between square brackets??

Luckily not:

select *
from syscolumns
where name like '[A-Z]%'
order by name

How about case-sensitive searches

Most often, a Microsoft SQL Database luckily is not case-sensitive. But there are situations where you might need to do a case-sensitive search.

Let's say your convention is to have all column names starting with uppercase. But others have been ignoring your convention and used lowercase instead. How can you identify those columns so you can rename them?

If a Database is case-sensitive or not depends on the collation used. By default a none case-sensitive collation is used. Luckily we can use a different collation in a query to go around the actual collation.

Here is a trick you can use.

select *
from syscolumns
where name collate Latin1_General_BIN like '[a-z]%'
order by name

Could this be an email address?

So let's end this one with a praticle example of how you can use the Like operator.

Let's say you have a table with a column that contains an email address. But in the application it is just a text field and users can put in it anything they want. How can you check if the value could actually be an email address?

Here is a trick you could use:

select *
from cicmpy
where cmp_e_mail like '%[A-Z0-9][@][A-Z0-9]%[.][A-Z0-9]%'

Now this one only checks if the value could be an email address. It does not tell you if it is an actual valid email address.

To know that for sure, the only real test is to send an email to that address from an application. But that is a whole different story!

That's it for today!

As you can see, there is much more to the Like operator than you might think. It's not just the % wildcard to loor for something that contains a value.

To be honest though, the % wildcard is the only one I use regularly in my actual Queries with reporting or applications. The Like operator is not the best performing operator, so it is better to avoid it in queries that run frequently.

But when it comes to troubleshooting and ad hoc queries, the Like operator might just be your best friend.

What's not to like about the Like? 😊

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!