Home > SQL Server Questions > Convert unicode Characters to be displayed in ASCII format – MSDN TSQL forum

Convert unicode Characters to be displayed in ASCII format – MSDN TSQL forum


–> Question:

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”
 

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

Eg: Ḩaḑramawt ?a?ramawt
 

–> Answer:

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 option.

For more info you can check my blog post on COLLATION, Collation Conflicts and Change a Database Collation.
 

Regarding your other problem, 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 result set.
 

Ref link.


Advertisement
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: