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]’),

(‘’),

(‘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:

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.

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

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.

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

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.

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

Filed under: Blog Tagged: SQLNewBlogger, syndicated, T-SQL