Question Re: Remove Accented Characters from a string in Technology Q&A

Remove Accented Characters from a string

Former Member — Wed, 07 Oct 2015 15:57:30 GMT

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.

Re: Remove Accented Characters from a string

VolkerBarth — Wed, 07 Oct 2015 16:31:19 GMT

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.

Re: Remove Accented Characters from a string

Vlad — Fri, 09 Oct 2015 05:28:56 GMT

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

Re: Remove Accented Characters from a string

Vlad — Mon, 12 Oct 2015 16:07:24 GMT

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?

Re: Remove Accented Characters from a string

VolkerBarth — Tue, 13 Oct 2015 02:22:09 GMT

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

Re: Remove Accented Characters from a string

VolkerBarth — Tue, 13 Oct 2015 03:17:41 GMT

Just a very wild guess:

AFAIK collation tailoring is based on the ICU library used within SQL Anywhere (dbicu.dll).

As ICU does also allow to transform data (including decomposition of characters), you might be able to make use of the ICU library yourself. That may be more reliable than a home-brewn list of "special characters to convert".