CODEWITHSUNDEEP
sqlsql-server-2008
Harry
Harry

Reputation: 11

How to convert foreign characters to English characters in SQL Query?

I have to create sql function that converts special Characters, International Characters(French, Chinese...) to english.

Is there any special function in sql, can i get??

Thanks for your help.

Upvotes: 1

Views: 4531

Answers (2)

David
David

Reputation: 25460

I think the short answer is you can't unless you narrow your requirements a lot. It seems you want to take a text sample, A, and convert it into romanized text B.

There are a few problems to tackle:

  • Languages are typically not romanized on a single character basis. The correct pronunciation of a character is often dependent on the characters and words around it, and can even have special rules for just one word (learning English can be tough because it is filled with these, having borrowed words from many languages without normalizing the spelling).

  • Even if you code rules for every language you want to support you still have homographs, words that are spelled using exactly the same characters, but that have different pronunciations (and thus romanization) depending on what was meant - for example "sow" meaning a pig, or "sow" (where the w is silent) meaning to plant seeds.

  • And then you get into the problem of what language you are romanizing: Characters and even words are not unique to one language, but the actual meaning and romanization can vary. The fact that many languages include loan words from those language they share characters with complicates any attempt to automatically determine which language you are trying to romanize.

Given all these difficulties, what it is you actually want to achieve (what problem are you solving)?

You mention French among the languages you want to "convert" into English - yet French (with its accented characters) is already written in the roman alphabet. Even everyday words used in English occasionally make use of accented characters, though these are rare enough that the meaning and pronunciation is understood even if they are omitted (ex. résumé).

Is your problem really that you can't store unicode/extended ASCII? There are numerous ways to correct or work around that.

Upvotes: 2

Ben
Ben

Reputation: 35613

If you are after English names for the characters, that is an achievable goal, as they all have published names as part of the Unicode standard.

See for example:

Your task then is to simply turn the list of unicode characters into a table with 100,000 or so rows. Unfortunately the names you get will be things like ARABIC LIGATURE LAM WITH MEEM MEDIAL FORM.

On the other hand, if you want to actually translate the meaning, you need to be looking at machine translation software. Both Microsoft and Google have well-known cloud translation offerings and there are several other well-thought of products too.

Upvotes: 2

Related Questions