-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|