Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    7

    Unanswered: case-sensitive 'replace'

    Hi,

    Is there a way to do a case-sensitive "replace" when the SQLServer default collation is case-insensitive?

    e.g.,

    1. select replace('ABC', 'a', 'd') -> 'ABC' (nothing gets replaced because 'a' does not match 'A'.

    2. select replace('ABC', 'A', 'D') -> 'DBC' ('A' gets replaced by 'D' because the cases of 'A' in the search string and the replace char match.

    Thanks in advance for your help.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    the quick answer would be:

    Code:
    select replace('ABC' COLLATE Latin1_General_CS_AS, 'a' COLLATE Latin1_General_CS_AS, 'd' COLLATE Latin1_General_CS_AS)
    select replace('ABC' COLLATE Latin1_General_CS_AS, 'A' COLLATE Latin1_General_CS_AS, 'd' COLLATE Latin1_General_CS_AS)
    however, I think I would write a function to take the replace arguments and collation type.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    May 2002
    Posts
    299

    Re: case-sensitive 'replace'

    This trick would work on sql7...

    select replace(cast('AbC' as varbinary),cast('b' as varbinary),cast('B' as varbinary))
    --
    -oj
    http://www.rac4sql.net

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •