Remove Accented Characters from a string

[#1]

Is there a simple way to remove accented characters from a string?

For example àéêöhello! needs to be converted to aeeohello!

In SQL server I would use Collate to accomplish this in one line. However, I am not able to find a solution that is working other than using multiple replace statements or something of the sort.

This is in a database where multiple languages are used and stored. I need to convert Portuguese to English when the data is being exported, but don't have the budget for a translator.

Your help is appreciated.

[VolkerBarth]

I guess the CSCONVERT function may be of help here.

UPDATE:

Say, converting to ASCII will turn accented characters into their unaccented base version - however it may also replace other characters by probably undesired results:

select cast(csconvert('àéêöhello!', 'ascii') as varchar)

returns "aeeohello!" as desired but a test with German unlauts reveals a misfit for the "ß":

select cast(csconvert('aäboöuüssßÄÖÜ', 'ascii') as varchar)

returns "aaboouuss\x1aAOU" (note: The character before the upper "A" is ASCII 26 and is not dispalyed here as a non-printable char).

---

Aside: What I had originally in mind with my suggestion was some kind of "collation tailoring" with csconvert(), i.e. using something like "AccentSensitivity=ignore" in the target charset.

For example you can use collation tailoring to ignore or respect accents when doing comparisons:

select compare('àéêöhello!', 'aeeohello!', 'uca(AccentSensitivity=ignore)'), compare('àéêöhello!', 'aeeohello!', 'uca(AccentSensitivity=Respect)')

returns 0 (= identical) vs. 1.

However, this will not work here as csconvert() uses a charset (and not a collation) as its arguments.

[Vlad]

Quite an interesting solution. Here was my request:

select 'aáéíóúb' as a, TO_CHAR(CSCONVERT(a, 'ASCII'))

And this was the response: aáéíóúb,a\x1a\x1a\x1a\x1a\x1ab

What is funny is that the characters between a and b are displayed as spaces (indeed they are a\\x1a\\x1a\\x1a\\x1a\\x1ab). Internet suggests a more generic solution: http://stackoverflow.com/questions/4024072/how-to-remove-accents-and-all-chars-a-z-in-sql-server

[Vlad]

Hm, I like your answer. But I do not know, why when I use TO_CHAR, I get a different result. Should I use CAST instead?

[VolkerBarth]

Well, as you do use the TO_CHAR() function without its second parameter, as to the docs, it should exactly do the same as a cast (though to char instead to varchar but that should not matter) - from the docs:

If source-charset-name is not specified, then this function is equivalent to:

CAST( string-expression AS CHAR );

Possibly we both do use a different database charset ("Windows-1252" in my case)?