I’ll be in France and largely AFK for a few weeks this summer, so I’m posting some golden oldies. You can find the original article and comments here.
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.