converting extended ascii characters

• jonathan.silver

  Old Hand

  Points: 347

  More actions

  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

• John Beggs

  SSCommitted

  Points: 1921

  More actions

  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')

• jonathan.silver

  Old Hand

  Points: 347

  More actions

  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.

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  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)