Home » Applied SQL » Recent Articles:

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

 

Applied SQL: Find and replace using regular expressions in SSMS

ssms_regexRegular expressions are searches on steroids, the wildcats of the wildcard world*.  Even if you  never ever write CLR, regex (as it’s affectionately known) can be useful to you today, right now.

  1. Open a new query window in SQL Server Management Studio.
  2. Type “xyz”, hit enter twice, and type “xyz” again.
  3. Now hit CTRL-H to pull up your Find and Replace dialogue.
  4. Under Find What, type \n\n. Under Replace With, type \n
  5. Select Options > Use > Regular Expressions.
  6. Hit Replace All. Watch your double spaced script magically turn single spaced.

This is the first of many simple uses for regex in SSMS. With just a little poking around online, you can find every reference to a table in a script, regardless of whether it’s bracketed or not ([stats].Tbl and stats.Tbl and stats.[Tbl], etc.).  Or, replace all instances of varchar(…) with varchar(500) in your stored procedure. Or, well, whatever you need!

Go on, play around with some regex. Check out Technet’s “Search Text with Regular Expressions” and Simple Talk’s “RegEx-Based Finding and Replacing of Text in SSMS” for more.

And…

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

P.S. As long as we’re on useful work habits, you should really Learn to Type and get to know common (and custom) keyboard shortcuts, too.

*Oh, what alliteration!!

Clarity in T-SQL: Adjoining Socks

One of my children is very literal, a true pedantic.

“Is there any homework you need to do?”

“No.”

“Really? Show me your homework sheet. … Why does it say ‘book report due’ for tomorrow?”

“Because the report is due tomorrow.”

[Deep breath.] “So why did you JUST tell me you don’t have any homework due.”

“Because I don’t have the book, and I can’t do the report. So I can’t do it.”

[More deep breaths, and a lengthy discussion following.]

As it turns out, you have to be very particular when talking to somebody as persnickity, as specific, as literal as that. Computers are even more persnickity, and so the questions you ask them have to be even more specific.

Querying? Be specific, Bob.

A T-SQL query is well named: a query is a question you’re asking the database. If you’ve been working with T-SQL for a while, you start to take queries for granted. You can write the code to find duplicates in your sleep. But sometimes, even “simple” queries take some thinking about. So, let’s think about them! Today, we’re thinking about joining tables.

In table 1, “Shoe”, we have a listing of all the shoes in your household. And table 2, “Sock”, holds all the socks.

Shoe: Brown shoe, Black shoe, Blue shoe

Sock: Brown sock, Black sock, White sock

We might need to ask the database to match shoes to socks based on size and color.

SELECT * FROM Shoe INNER JOIN Sock ON Shoe.Color = Sock.Color;

Simple and easy. We’re JOINing the two tables, and our criteria is Color=Color. No problem.  We might get back the Shoe/Sock pairs

Brown Shoe/Brown Sock

and

Black Shoe/Black Sock

Okay, now what if we want ALL the shoes, and the socks of matching color? Be specific there, Bob. We want all the shoes, whether or not they have a match, and matching socks where they exist. Pretty easy too, ish.

SELECT * FROM Shoe LEFT OUTER JOIN Sock ON Shoe.Color = Sock.Color;

That’s what left outer joins are for, right? It gets us everything from one table, regardless of the matching criteria. This query will get us the right answer, which is

Brown Shoe/Brown Sock

and

Black Shoe/Black Sock

and

Blue Shoe/NULL

Going Awry

Here’s where I’ve seen some people get tripped up. What we want now is all shoes that have NO matching color in the Sock table. The temptation is to write something like this:

SELECT * FROM Shoe INNER JOIN Sock ON Shoe.Color <> Sock.Color;

That seems to make sense. Sorta. On second thought, what we wrote here actually says “give me all mismatched pairs”. This query returns every iteration of mismatches: Brown/Black, Brown/White, Black/Brown, Black/White, Blue/Brown, Blue/Black.

Asking the right question

Okay, let’s rethink this. We’ve already seen we can get data from one table where there’s not a match, right? We used an OUTER JOIN for that.

SELECT * FROM Shoe LEFT OUTER JOIN Sock ON Shoe.Color = Sock.Color;

That gave us Brown/Brown, Black/Black, and Blue/NULL.  Soooo, if all we need to do now is get rid of those matches, then…well, SAY so!

SELECT * FROM Shoe LEFT OUTER JOIN Sock ON Shoe.Color = Sock.Color WHERE Sock.Color IS NULL;

This will return Blue/NULL.

Let’s take it one step further: We want to find shoes with no matches in Sock, and socks with no matches in Shoes. Well heck, that’d be a FULL outer join!

SELECT * FROM Shoe FULL OUTER JOIN Sock ON Shoe.Color = Sock.Color WHERE Sock.Color IS NULL OR Shoe.Color IS NULL;

Note we didn’t tell it WHERE Shoe is null AND Sock is NULL. That’s a great way to always get exactly zero rows returned. We need either Sock OR Shoe to return NULL, not both.

That’s about it, kids. And if any of you truly do keep an inventory of your socks and shoes in an RDBMS, I certainly hope it’s multithreaded.

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

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!

Where are We?

April 14: Oklahoma City SQL UG's 5th Anniversary
April 28-30: Powershell Summit in Bellevue, WA
May 10: SQL Saturday Houston
August 2: SQL Saturday Baton Rouge (planned)
August 22-23: SQL Saturday Oklahoma City Precon!

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/