Applied SQL: LIKE ‘whatever’

SSMS_practiceToday let’s play around with the LIKE operator. Like, totally.

Like, Introduction

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
FROM Review
WHERE Comments LIKE ‘%worst%’;

Also, when we’re searching for all product names that begin with the word “Hex”:

SELECT ID, [Name]
FROM Product
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
FROM sys.sql_modules
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:

SELECT [Name]
FROM Department
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:

SELECT petName
FROM myPet
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:

SELECT petName
FROM myPet
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.

Happy days,
Jen McCown
MidnightDBA.com/Jen

 

4 thoughts on “Applied SQL: LIKE ‘whatever’

  1. Kenneth Fisher

    One of my favorites is ^ .

    When used in inside the []s it means NOT. So

    SELECT petName
    FROM myPet
    WHERE petName LIKE ‘[^D-R]ex’;

    Would return Bex or Wex but not Dex, Hex etc.

    1. Jen McCown Post author

      Ooh, now that’s just cool, and very regular-expresssions-y. I wonder how many other regex things would work inside the brackets….hmm….

      [researching….]

      As it turns out, not much.

  2. Pingback: SQL for Everybody | The MidnightDBA Star-Times

  3. Pingback: From the Vault: Applied SQL: LIKE ‘whatever’ | The MidnightDBA Star-Times

Comments are closed.