#SQLNewBlogger – T-SQL ESCAPE for Wildcards

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I ran into a really interesting issue recently. I was working with a table and wanted to determine if the first character of a string was a left bracket. However, I discovered searching for a bracket isn’t as simple as I expected.

Setup

Here’s a mock table and some data.

CREATE TABLE MyData
( myid INT IDENTITY(1,1),
mychar VARCHAR(50)
);
GO

INSERT dbo.MyData
(mychar)
VALUES
(‘This is a string’),
(‘”A Quoted String”‘),
(”’Single quoted string”’),
(”’more single quotes”’),
(‘[My bracketed string]’),
(‘[I like brackets]’),
(‘Can I find [this] string?’)
;
GO

I wanted to return only rows 5 and 6 (based on identity) and not the others. My first thought was that I could just make a simple query.

SELECT myid, mychar FROM mydata WHERE mychar LIKE ‘[%’

The results:

2016-11-11 18_20_37-SQLQuery5.sql - localhost_SQL2016.sandbox (PLATO_Steve (63))_ - Microsoft SQL Se

That didn’t work. As soon as I got zero rows, I remembered that brackets allow me to wildcard part of a query. I need to escape the bracket, so I decided to try and do that with a repeating character, as we do with quotes.

2016-11-11 18_21_41-SQLQuery5.sql - localhost_SQL2016.sandbox (PLATO_Steve (63))_ - Microsoft SQL Se

Still not working. I searched, and there is an escape character of a backslash as well, but that didn’t help.

2016-11-11 18_25_01-SQLQuery5.sql - localhost_SQL2016.sandbox (PLATO_Steve (63))_ - Microsoft SQL Se

Now I was really curious. I checked the page for LIKE, and saw there was an ESCAPE option. I never knew this existed. I read the entry, but then when I looked at the samples, I was slightly confused. Why were they using an exclamation point?

I read further and realized I hadn’t paid close attention. The escape character is the one I want to match up before the character I’m escaping. So I need to escape the trigger I’m going to use in front of the bracket.

This is easier to show than explain. Here’s what I first did.

2016-11-11 18_24_42-SQLQuery5.sql - localhost_SQL2016.sandbox (PLATO_Steve (63))_ - Microsoft SQL Se

It looks like I’m escaping the bracket, but then why do I need two of them? If I remove one bracket, this doesn’t work (shown here).

2016-11-11 18_26_32-SQLQuery5.sql - localhost_SQL2016.sandbox (PLATO_Steve (63))_ - Microsoft SQL Se

What happens is the first left bracket is a trigger for the compiler to evaluate the next bracket as a literal, not a wildcard. If I replace the first bracket and the parameter with the exclamation point, this makes sense.

2016-11-11 18_26_50-SQLQuery5.sql - localhost_SQL2016.sandbox (PLATO_Steve (63))_ - Microsoft SQL Se

It’s not often I’ve had to search for brackets, but checking for percent signs has been common, and this is handy. I can’t believe I’ve never had to do this.

#SQLNewBlogger

Once I played with this in my code, I realized this was a neat function. I mocked up the table and it took longer to just type the words around the code than actually figure things out.

This would be a nice short type of post for those of you that want to show you’ve learned a small thing.

References

LIKE – https://msdn.microsoft.com/en-us/library/ms179859.aspx

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

One Response to #SQLNewBlogger – T-SQL ESCAPE for Wildcards

  1. Interesting point on escape characters Sir, must admit I would have gone with this option

    SELECT MD.myid
    ,MD.mychar FROM dbo.MyData AS MD
    WHERE CHARINDEX(‘[‘ ,MD.mychar) =1

    So many ways to SQL a problem

Comments are closed.