Special Character '[' in MSSQL 2005

Hi,

I am using MSSQL 2005 with the compatability mode set to 2000.Now, one of my String columns in a table has data in the following format:

ABC[123][XYZ]

Now issuing the following SELECT query in the table does not work :

Select * from TAB1 where col1 like 'ABC[%'

ie, no row gets selected.However, the following query works:

Select * from TAB1 where col1 like 'ABC%'

Looks like '[' is a special character :( Now, how do i work around this so that i can select strings starting with "ABC[".Please help.

Thanks.

You can try with escape character, like:

Select * from TAB1 where col1 like 'ABC![%' ESCAPE '!'

--first try this:

SELECT 'works' AS msg
WHERE 'ABC[123456]' LIKE 'ABC[%'

--and then try this

SELECT 'works' AS msg
WHERE 'ABC[[123456]' LIKE 'ABC[[%' ESCAPE '['

--or this
SELECT 'works' AS msg
WHERE 'ABC[[123456]' LIKE 'ABC$[%' ESCAPE '$'

--or this
SELECT 'works' AS msg
WHERE 'ABC[[123456]' LIKE 'ABC*[%' ESCAPE '*'

--in general "[" character is a special character, used to specify a range for a pattern matching (i don't believe it has something to do with the compatibility level)

--HTH--

Hi,

I was just wondering if there are any default escape characters in MSSQL ??, ie, i should be able to escape a character without using the "ESCAPE <character>" command in my SQL statement.

For example, assume the character "!" is the default character in MSSQL, then i should be able to do the following :

Select * from TAB1 where col1 like 'ABC![%'

Is there any way to achieve the above ??

Thanks !

To use a special character as a literal character in MSSQL,you need to enclose the special character in brackets.For example,

Select * from TAB1 where col1 like 'ABC[ [ ]%'

The above command removes the neccessity to use the "ESCAPE <character>" command in the SQL statement !

There is no default escape character.