Results 1 to 6 of 6

Thread: Special Character '[' in MSSQL 2005

  1. #1
    Join Date
    Oct 2007
    Posts
    6

    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.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    You can try with escape character, like:

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

  3. #3
    Join Date
    Sep 2005
    Posts
    168
    --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--

  4. #4
    Join Date
    Oct 2007
    Posts
    6
    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 !

  5. #5
    Join Date
    Oct 2007
    Posts
    6
    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 !

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    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
  •