CODEWITHSUNDEEP
sql-serverregexstored-proceduresnon-ascii-characters
DeanMWake
DeanMWake

Reputation: 923

Replacing non-ascii characters in MSSQL

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

Upvotes: 2

Views: 14749

Answers (3)

Hanni Ali
Hanni Ali

Reputation: 399

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

Upvotes: 2

thomasb
thomasb

Reputation: 6037

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.

Upvotes: 0

CaptainRabbit
CaptainRabbit

Reputation: 25

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.

Upvotes: 0

Related Questions