Post reply

converting extended ascii characters

  • September 26, 2011 at 5:09 pm

    #243783

    We download to a system that supports limited ascii (US) character set. I've added some translations to convert

    extended ascii codes like À to A. However, I find some of the translations are not working. For example

    why does this result in Y

    select REPLACE(CHAR(228),CHAR(196),'Y')

    thanks,

    jon

  • September 26, 2011 at 5:49 pm

    #1386886

    Likely this is caused by your default collation being case-insensitive.

    Try adding a collation statement to your script.

    e.g. REPLACE(CHAR(228) COLLATE Latin1_General_BIN, CHAR(196), 'Y')

  • September 26, 2011 at 6:15 pm

    #1386887

    Wow, that's really interesting. Thanks.

    Is the collation setting by user or server based?

    and then, if I wanted to make sure all of those funny ascci codes -

    ¡ ¢ £ ¤ ¥ ¦ § ¨ © ª « ¬ ­ ®

    were replaced with a space, would I be able to use the same Latin1_General_BIN

    code page?

    thanks again, I never would have figured this out.

  • September 26, 2011 at 8:07 pm

    #1386899

    jonathan.silver (9/26/2011)

    Wow, that's really interesting. Thanks.

    Is the collation setting by user or server based?

    and then, if I wanted to make sure all of those funny ascci codes -

    ¡ ¢ £ ¤ ¥ ¦ § ¨ © ª « ¬ ­ ®

    were replaced with a space, would I be able to use the same Latin1_General_BIN

    code page?

    thanks again, I never would have figured this out.

    There's a default at the server level that can be overridden at the database level which may be overriden at the column level when creating a table or view or in an SQL Statement. I'm not sure that you can set COLLATION by user... I don't believe so but I've never done any research in that particular area.

    Yes... Latin1_General_Bin will help you replace "funny" ASCII code characters.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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