Why is the T-SQL "LIKE" operator not evaluating this expression like I think it should?

Question

I am attempting to error trap a T-SQL variable name by making sure that the value of the variable is prefixed with a bracket "[".

Here's an example of how I am trying to do this:

DECLARE @thing nvarchar(20)
SET @thing = '[55555'
IF(@thing NOT LIKE '[' + '%') --If the value does not start with [ then add it
BEGIN
SET @thing = '[' + @thing
END
PRINT @thing

The example above PRINT's [[55555

Notice that the original value of @thing was prefixed with the bracket "[". I was expecting the IF condition would have returned false since "[55555" is LIKE '[' + '%'

Why is the IF condition not returning false? And, more importantly I suppose, what is the correct syntax to check for the existence of a string that occurs at the beginning of a variable string value?

EDIT It appears as there is something special about the bracket "[". When I run LIKE on a bracket it doesn't do what I expect, but when I don't use a bracket the LIKE works the way I expect.

Check out these examples:

IF('[' NOT LIKE '[')
BEGIN
PRINT '[ is NOT LIKE ['
END
ELSE
BEGIN
PRINT '[ is LIKE ['
END

IF('StackO' NOT LIKE 'StackO')
BEGIN
PRINT 'STACKO is NOT LIKE StackO'
END
ELSE
BEGIN
PRINT 'STACKO is LIKE StackO'
END

Here's the output of the two conditions:

[ is NOT LIKE [

STACKO is LIKE StackO

Proctor

Any difference if using '[%' and not the form with concatenation? – Rebbecarebbecca

No. No difference. Actually, on my first attempt I used your version. In trying different syntax's I forgot to move it back to the og '[%' prior to posting. – Proctor

Answer 1

I believe it may be because '[' is actually part of the LIKE operators syntax, as defined here: http://msdn.microsoft.com/en-us/library/ms179859.aspx

You need to define an escape character to escape the [, like this:

DECLARE @thing nvarchar(20)
SET @thing = '[55555'
IF(@thing NOT LIKE '\[%' ESCAPE '\' )
BEGIN
SET @thing = '[' + @thing
END
PRINT @thing

An alternative solution would be the following:

DECLARE @thing nvarchar(20)
SET @thing = '[55555'
IF(LEFT(@thing,1) <> '[') --If the value does not start with [ then add it
BEGIN
SET @thing = '[' + @thing
END
PRINT @thing

Engrossment

Answer 2

To get it working change your check to

IF (SUBSTRING(@thing, 1,1) != '[')

The reason why the like is not working is because [ is a special char in like. just like % is. See here

Kalasky

Thanks for your answer - You'll notice you all had the same principled answer and since I could only pick one, I randomly chose Tom. – Proctor

Answer 3

Bracket characters ([ and ]) are special wildcard characters in T-SQL. To search for those literal characters, you'll want to escape those characters (indicate that you want to search for those literal characters, rather than employing them as wildcards). Use ESCAPE to do this, like so:

DECLARE @thing nvarchar(20)
SET @thing = '[55555'
-- pick an escape character you won't see in your content
IF(@thing NOT LIKE '![' + '%' ESCAPE '!')
BEGIN
  SET @thing = '[' + @thing
END
PRINT @thing

This prints [55555.

From MSDN:

You can search for character strings that include one or more of the special wildcard characters... To search for the percent sign as a character instead of as a wildcard character, the ESCAPE keyword and escape character must be provided. For example, a sample database contains a column named comment that contains the text 30%. To search for any rows that contain the string 30% anywhere in the comment column, specify a WHERE clause such as WHERE comment LIKE '%30!%%' ESCAPE '!'.

Isotherm

Thanks for your answer - You'll notice you all had the same principled answer and since I could only pick one, I randomly chose Tom. – Proctor

Answer 4

You have to escape special characters (brackets, single quotes, etc.). In this case, you could do this:

LIKE '[['

EDIT:

PS -- [ is a special character because it can be used for wildcards, like this: LIKE '[0-9]' to do pattern-matching. (In this case, the match is like a regex -- any digit between 0 and 9.

Occidental