Replacing non-ascii characters in MSSQL

Question

I have done quite a bit of research and can not seem to find any answers. What I need to do is the following:

REPLACE all characters in a MSSQL column which are non ascii characters with their ascii equivalents. For example:

ë --> e
ï --> i
ñ --> n

I have read through the following which may seem similar but are, instead of replacing, eliminating/removing these characters (which is not ideal).

How do I remove extended ASCII characters from a string in T-SQL?

Purpose of [^\x20-\x7E] in regular expressions

http://jamesveitch.com/t-sql-function-replace-non-printable-ascii-chars/

http://www.sqlservercentral.com/Forums/Topic853088-149-1.aspx

Thanks to @Eric below and the selected answer.

(Helpful reference: How to remove accents and all chars <> a..z in sql-server?)

You may reference to this http://stackoverflow.com/a/12715102/1287352 — Eric, Jun 01 '15 at 09:31
@Eric Thanks, that seems to be the solution. The same as what people have suggested below. — DeanMWake, Jun 01 '15 at 09:49

score 1 · Answer 1 · answered Oct 08 '16 at 20:44

For those that come across this, as I did, I find the below to be simplest, it's even fairly simple to update multiple columns in one inplace statement, make sure to set the varchar size to match the columns you change:

UPDATE myTable
SET [Column1]   = cast([Column1] as varchar(10)) collate SQL_Latin1_General_Cp1251_CS_AS,
    [Column2]   = cast([Column2] as varchar(20)) collate SQL_Latin1_General_Cp1251_CS_AS
FROM myTable

score 0 · Answer 2 · answered Jun 01 '15 at 08:47

0

set a value for for your starting symbol or the equivalent value in ascii table. And start a loop and replace all values in your new code.

answered Jun 01 '15 at 08:47

CaptainRabbit

25
6

score 0 · Accepted Answer · answered Jun 01 '15 at 08:57

You cannot do it any other way than the old-fashioned, "hard" way (in any language, even, not only SQL).

Since in many (spoken/written) languages, accented characters are not the same as non-accented ones, it's actually just a visual similarity, so there is no true correspondance. Some letters can look like just the same with a symbol added, but actually have a completely different "sound" and different rules (example).

Just build a table, or array, or whatever of the correspondances you know, or find, or can think of.

char | replacement
------------------
à    | a
è    | e
é    | e
ñ    | n
ç    | c
ß    | B
...

Then, loop on your data and replacement chars to replace all characters by their correspondance.

Thanks, using this and the reference from Eric above we are creating a basic function to do this. — DeanMWake, Jun 01 '15 at 09:49

Replacing non-ascii characters in MSSQL

3 Answers3