How many times do you use the function LIKE? Probably more than you think. I use it quite often. Especially dealing with customer related information where I only have information on a piece(s) of the data.
Anyway, about a month or so ago (maybe longer), I was looking at the LIKE function in books online when I noticed that there where several different match patterns that you can utilize to find what your looking for. I must admit, I didn’t know that! Oh the joy’s of learning something new! Even as trivial as this!
So, here’s the skinny. There are 4 different match patters that you can use, shown below. Books Online has pretty good information on them.
Your Standard ‘%’
This is the one that is probably the most used. The usage is fairly straight forward and functions just like the ‘*’ wildcard in DOS. If you were looking a last name that started with ‘sm’ you’d use this type of syntax:
SELECT Title, FirstName, LastName FROM Person.Contact WHERE LastName LIKE 'sm%'
This query would look for any last name that started with ‘sm’ regardless of the length of the column. If you altered the query to this:
SELECT LastName FROM Person.Contact WHERE LastName LIKE '%sm%'
It would return a list of rows that there last name contained ‘sm’, regardless of what is in front or behind it. For example, you’d get the name of ‘Huntsman’ returned where the prior query would return ‘smith’. Straight forward and very common right? Yup, thought so. Let’s look at the next one.
The Mighty Underscore (_)
The underscore, when used with LIKE is a wild card for a single character. So if you were looking for a 5 character name ending in “ith” you could have a query like:
SELECT Title, FirstName, LastName FROM Person.Contact WHERE LastName LIKE '__ith'
Of course you’d get a lot of records for the name of “smith”. Of course, this is only handy if you know that you’re looking for a 5 character name.
The Powerful [ ]
This wild card will look for any single character within the range that you tell it. In other words, if you had the query:
SELECT Title, FirstName, LastName FROM Person.Contact WHERE FirstName LIKE '[a-m]on'
it would look for any first name that ended in ‘on’ and began with any letter from ‘A’ to ‘M’. So you might get results like ‘Don’ or ‘Jon’. I can see where this would come in handy, especially if you had a large data set and was looking for a range of names but didn’t want all of the ‘on’ names returned.
The Awesome [^]
This one is the reverse of the one above, the . It will look for any character NOT within the specified range, or if you’d like, a specified set of characters. If you had this query:
SELECT Title, FirstName, LastName FROM Person.Contact WHERE LastName LIKE 'De[^l]%'
it would look for any last name beginning with ‘De’, the next letter is NOT ‘L’ and everything behind that character can be anything, hence the wild card ‘%’. Here is the result set:
The Nitty Gritty
So, those are the four match patterns that ‘LIKE’ will accept. Now if you’re like me, you’re wondering what the execution plan looks like. Here’s the nitty gritty:
Going on a tangent. You’ll notice a couple of key lookup’s in three of the four plans. The index used in these queries only contains the LastName column and not the FirstName nor the Title. I adjusted the index in question to include the FirstName and Title columns and the key lookup’s went away.
As you can see from the queries above, the LIKE function is very versitle and has many uses. I plan on using some of these newly discovered features when the time comes. Goes to show that it doesn’t hurt to pick up a book (even if it’s one online!) and read up on functions that you thought that you knew. You never know, what you read might just surprise you!
It did for me.
© 2011, John Morehouse. All rights reserved.