Today let’s play around with the LIKE operator. Like, totally.
Like “determines whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters.” That makes it pretty darn useful, say, when we’re searching for all product reviews with the word “worst” in it:
SELECT ID, Comments
WHERE Comments LIKE ‘%worst%';
Also, when we’re searching for all product names that begin with the word “Hex”:
SELECT ID, [Name]
WHERE [Name] LIKE ‘Hex%';
We can even use multiple % wildcards to find strings where something is followed by something else. This is particularly useful, I find, when searching sys.sql_modules for code within stored procedures:
SELECT object_id, definition
WHERE definition like ‘%DELETE%Customer%';
You get the idea. You can also, of course, find strings that don’t match a certain pattern, using NOT LIKE. For example, let’s say we want all departments that aren’t production related:
WHERE [Name] NOT LIKE ‘%Production%';
Like, Really Cool
But wait, there’s more!
Go back and look over that TechNet article on LIKE. Check out the table in the “pattern” section. We’re familiar with the % wildcard – “Any string of zero or more characters”. But pay attention to the next one: the underscore (_), which represents a single character.
Now we can do things like, oh, getting all strings that begin with “t” and are exactly five characters long:
WHERE petName LIKE ‘t____';
You can, of course, use WHERE LEN(petName) = 5 AND petName LIKE ‘t%’ instead. Nothing’s stopping you. But never overlook the beauty of being able to do things in more than one way.
Edit: Someone over on Reddit pointed out that this comment deserves a little more explanation. He’s right: you should note that LEN(petName)=5 makes the predicate non-sargable, meaning that that bit won’t be able to take advantage of a useful index. However, petName LIKE ‘_____’ is ALSO non-sargable, though it does seem to provide a better estimated number of rows, which would potentially result in a better plan.
In our scenario, this is all slightly moot, because that leading t IS a sargable search term: it will use an appropriate index, if available, to seek out data. Even so, I’d probably go with LIKE ‘t____’, knowing what I know now about the estimated rows accuracy.
We also have the ability to search for character ranges:
WHERE petName LIKE ‘[D-R]ex';
This will return any instances of Dex, Hex, Lex, Mex, Rex, and so on…but not Bex or Wex.
Go thou, and and play around with, like, whatever.