Why doesn't the T-SQL statement "LIKE" evaluate this expression as I think?

Question

I am trying to catch an error in a T-SQL variable name by making sure that the variable value is prefixed with the brackets "[".

Here is 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 

Example above PRINT [[55555

Note that the original @thing value was prefixed with the brackets "[". I expected the IF condition to return false since "[55555" is LIKE '[' + '%'

Why doesn't the IF condition return false? And, more importantly, I believe that the correct syntax is to check for the existence of the string that occurs at the beginning of the value of the string variable?

EDIT It looks like there is something special in the bracket. When I run LIKE on a bracket, it does not do what I expect, but when I do not use a bracket, LIKE works as 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 is the conclusion of two conditions:

[I DO NOT LIKE [

STACKO LIKE StackO

Answer 1

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

You need to define an escape character to avoid [, for example:

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

An alternative solution might 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 

Answer 2

To make it work, change your check to

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

The reason this does not work is because [is a special char like. as%. See here

Answer 3

Bracket characters ( [ and ] ) are special wildcards in T-SQL . To search for these alphabetic characters, you will want to avoid these characters (specify that you want to search for these alphabetic characters and not use them as wildcards). Use ESCAPE for this, for example:

 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 

Will print [55555 .

From MSDN :

You can search for character strings that include one or more special wildcards ... To search for the percent sign, you must specify the ESCAPE keyword and the escape character instead of the wildcard character. For example, a sample database contains a column called comment that contains 30% text. To search for any rows containing a 30% row anywhere in the comment column, specify a WHERE clause, for example WHERE comment LIKE '%30!%%' ESCAPE '!' .

Answer 4

You need to avoid special characters (brackets, single quotes, etc.). In this case, you can do this:

 LIKE '[[' 

EDIT:

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