none
T SQL - Convert unicode Characters to be displayed in ASCII format RRS feed

  • Question

  • Hi,

    Is there a function (or any other way) in T SQL, by which I can somehow convert a unicode character (UTF - 8) and then display it in normal ASCII format?


    Eg: I want to convert "Ha Nội" to "Hanoi" either programatically or in SQL.
    Another Example:  Réunion as "Reunion"

    Thursday, March 17, 2011 9:33 AM

Answers

  • This is related to database COLLATION settings.

    This would not be the best way, but just in case an idea to sail you through....

    select 'Réunion', cast('Réunion' as varchar(100)) COLLATE SQL_Latin1_General_CP1253_CI_AI
    

    ... you can also play with "COLLATE DATABASE_DEFAULT" setting.


    For more info you can check Kalman's blog post on COLLATION here .


    ~Manoj (manub22@gmail.com)
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    • Proposed as answer by DVR Prasad Thursday, March 17, 2011 10:25 AM
    • Marked as answer by SQLDev555 Thursday, March 17, 2011 10:55 AM
    Thursday, March 17, 2011 10:02 AM

All replies

  • This is related to database COLLATION settings.

    This would not be the best way, but just in case an idea to sail you through....

    select 'Réunion', cast('Réunion' as varchar(100)) COLLATE SQL_Latin1_General_CP1253_CI_AI
    

    ... you can also play with "COLLATE DATABASE_DEFAULT" setting.


    For more info you can check Kalman's blog post on COLLATION here .


    ~Manoj (manub22@gmail.com)
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    • Proposed as answer by DVR Prasad Thursday, March 17, 2011 10:25 AM
    • Marked as answer by SQLDev555 Thursday, March 17, 2011 10:55 AM
    Thursday, March 17, 2011 10:02 AM
  • Thanks, thats brilliant.

     

    One other problem is sometimes some characters in unicode come up as "?" when cast using the above method:

     

    Eg -->  Ḩaḑramawt ?a?ramawt

     

    Other than that this is a very good idea.

     

    Thanks Again.

    Thursday, March 17, 2011 10:08 AM
  • you are brilliant
    DVR
    Thursday, March 17, 2011 10:25 AM
  • Pramod, You want to match the foreign language characters to English, right? Which is not possible in most of the cases.

     

    Just like in French, Réunion, contains 'é' which is similar to English 'e' but with l'accent aigue accent.

    But this is not feasible for every character, and other languages too, like Chinese, Hindi. How can you map, and even if you map what symbol will it show? And thus every character cannot be matched against English and you'll see invalid characters in your resultset.

    That's what I think.

     

    Looking for some expert comments on this post.


    ~Manoj (manub22@gmail.com)
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Thursday, March 17, 2011 10:30 AM
  • Yes I agree.

     

    A lot of characters which do not have any straight english translations obviously show up as "?".

     

    I dont think there is any solution for this.

    Thursday, March 17, 2011 10:55 AM