The Power of Like
To find specific matches with an SQL Query, you will mostly use the comparison operators.
Like Equal To, Not Equal To, Greater Than of Lesser Than.
Especially with String Values, comparing might not be enough.
In comes the Like Statement.
Now the Like Statement is not that unknown in itself.
But it has some nice features you might not be aware of.
Here are some examples.
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.
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.
where name like 'sys%'
-- Ends with:
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"
where name like 'idm__or'
Both wildcards can also be used simultaneously:
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:
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:
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:
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:
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??
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.
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:
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 email@example.com. Let me know if you got inspired and May the Source be with you!