Home » Applied SQL »Beginner »Dev »sqlserverpedia-syndication »SSC »Tech and Learning » Currently Reading:

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

 

Currently there are "4 comments" on this Article:

  1. 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.

    • Jen McCown says:

      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. […] other Cool Things: the greatness of Powershell, XCKD, New Skin Liquid Bandages, standing desks, using underscores with LIKE in T-SQL, the Pandemic board game, Adam Savage’s Tested.com podcast, and using RegEx in […]

  3. […] 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. […]

Comment on this Article:







Minion Reindex by MidnightDBA is here!

 

Excellent Index Maintenance

Download Minion Reindex, log feature requests, read documentation, and sign up for the newsletter at MidnightSQL.com/Minion!


 

Where are We?

November 3-7: PASS Summit, Seattle, WA

Novemmber 2: #SQLLongRun

November 3, 4-6pm: meetup with the MidnightDBAs at Top Pot Doughnuts on 5th Ave.

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/