Post reply

Strange behavior

  • March 14, 2006 at 7:19 am

    #168772

    I have the following three queries:

    SELECT   replace('ABþDE', char(254),'C')

     

    SELECT   replace('Thys', char(104),'C')

    SELECT   replace('Thys', char(254),'C')

    the output of the first 2 is as expected:

    ABCDE

    TCys

    But number 3 startled me:

    Cys

    This is of course a rather big problem in some databases.

    The system we run this on :

    --SqlServer Standard Edition 8.00.2039(SP4)

    --Server language English(United States)

    --Server collation Latin1_General_CI_AS

    I would appreciate any help anyone has to offer.

    Thanks

     

  • March 14, 2006 at 7:38 am

    #626500

    Sorry but I can't reproduce your results.

    Are you taking those values from a column or you just typed them in QA?

     


    * Noel

  • March 14, 2006 at 9:27 am

    #626577

    Hi,

    Acoording to me it is Working fine,,

    When you run the query :

    select char(254)

    it willl return : 'þ'

    In your last Query your string does not contain 'þ'

    character so, it is returning your String As it is.

     

    Regards,

    Amit Gupta

     

     

  • March 14, 2006 at 9:40 am

    #626580

    It's do with the way the Þ Icelandic Thorn (222,254)  character sounds. It sounds like TH  so  "Þe" and "The" would sound the same. The ß character in German sounds like SS and also performs the same way

    Try the examples below

    SELECT   'TH = ' + char(222),replace('The both stood on the burning deck missing peanuts by the peck', char(222),char(222))

    SELECT   'TH = ' + char(254),replace('The both stood on the burning deck missing peanuts by the peck', char(254),char(254))

    SELECT   'SS = ' + char(223),replace('Miss Simpson heard the snake hiss on the strasse', char(223),char(223))

    Theres a fairly detailed discussion of it here

    http://www.miclasificado.com.ar/cgi/News/i64/Replace_function_doesnt_work.php

    It's an eye opener to see replace works that way.

    John H

  • March 17, 2006 at 4:29 am

    #627239

    Thanks John,

    works like a charm.

    I also took a look at the collation coercion rules in BOL, to see wich other functions are affected.

    Just to keep it in mind for the future.

    Marc

     

  • March 17, 2006 at 6:36 am

    #627287

    Thanks for the coercion rules hint Marc. We use char 254 a lot but never came across this problem it's given us a nice heads up. We can nip any potential problems in the bud before we come across them.

    John H

Viewing 6 posts - 1 through 5 (of 5 total)

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