Searching for a bracket ‘[‘ in a T-SQL string

In a Stored Procedure that I was working on, I needed to error trap a string variable to make sure that the value of the variable started with an opening bracket ‘[‘ and ended with a closing bracket ‘]’. My initial SQL looked something like this:

-- SQL example to prefix/suffix a string with brackets if they don't already exist
DECLARE @myString nvarchar(20)
SET @myString = '[ABC'
IF(@myString NOT LIKE '[%') -- If @myString doesn't start with '[' then prefix @myString with a '['
BEGIN
SET @myString = '[' + @myString
END

IF(@myString NOT LIKE '%]') -- If @myString doesn't end with ']' then suffix @myString with a ']'
BEGIN
SET @myString = @myString + ']'
END

PRINT @myString

To my surprise and confusion, the code example above did not result in the way that I thought it would. The output of the PRINT function resulted with:
[[ABC]

Note that the initial value of @myString was “[ABC”. I was expecting the first IF condition to return FALSE since @myString IS LIKE the expression ‘[%’. However, you’ll notice that the final output is “[[ABC”. Which means the first IF condition returned TRUE. Why?

The reason for this apparent bug is due to the fact that the brackets ‘[‘ and ‘]’ are special wildcard characters that can be used in conjunction with the LIKE Operator. In T-SQL you can use brackets to search a string for

Any single character within the specified range ([a-f]) or set ([abcdef]).

For more information on the bracket wildcard, go here.

Since the brackets are wildcards for the LIKE Operator, I needed to ESCAPE the bracket characters so that T-SQL would treat them as regular text. For more information on SQL Escape, go here.

With the help of my friends over at Stack Overflow, I learned that once I ESCAPE’d the brackets, my T-SQL code worked exactly how I thought it should. Here is the updated SQL that include the ESCAPE sequence:

-- SQL example to prefix/suffix a string with brackets if they don't already exist (this time with the ESCAPE sequence)
DECLARE @myString nvarchar(20)
SET @myString = '[ABC'
IF(@myString NOT LIKE '![%' ESCAPE '!') -- If @myString doesn't start with '[' then prefix @myString with a '['
BEGIN
SET @myString = '[' + @myString
END

IF(@myString NOT LIKE '%!]' ESCAPE '!') -- If @myString doesn't end with ']' then suffix @myString with a ']'
BEGIN
SET @myString = @myString + ']'
END

PRINT @myString

The output of the PRINT function resulted with:
[ABC]
Just as I expected.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s