Post reply

Replacing case sensitive letters on case insensitive server

  • November 28, 2005 at 7:06 am

    #90035

    Could someone help me with this, on first sight, easy question?
    How can I use TSQL replace function and achieve conversion of only small, or big letters on case insensitive server, database etc. without changing collation property.
    Why with this query I get the same result ?
     
    select replace(cast('A' as varbinary), cast('A' as varbinary), 'a')
    Result: a (that's OK)
    select replace(cast('A' as varbinary), cast('a' as varbinary), 'a')
    Result: a (not OK, I want A)
     
    It doesnt work niether on char, varchar, ascii conversion etc.
     

  • November 28, 2005 at 8:29 am

    #606127

    You can specify a casesensitive collation for each element in the replace, that way the replace will be casesensitive.

    select replace('A' collate Latin1_General_CS_AS, 'a' collate Latin1_General_CS_AS, 'a' collate Latin1_General_CS_AS)

                      

    --------------------

    A

    (1 row(s) affected)

    /Kenneth

  • November 28, 2005 at 9:54 am

    #606150

    Or you could use "Upper" & "Lower"...something like this...

    SELECT REPLACE(UPPER('a'), 'A', LOWER('A')) AS UpperTOLower

SELECT REPLACE(LOWER('A'), 'a', UPPER('A')) AS LowerTOUpper
UpperTOLower                   
------------------------------ 
a

(1 row(s) affected)

LowerTOUpper                   
------------------------------ 
A

(1 row(s) affected)







    **ASCII stupid question, get a stupid ANSI !!!**

  • November 28, 2005 at 12:14 pm

    #606191

    Thak you both on the fast reply.
    It was very helpfull.
    I was hoping on a string level collation, so I'll stick with the Kenneth's solution, and also because I need replace in a mixed case words.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply