How can I remove accents on a string?

Viewed 74454

I have the following string

áéíóú

which I need to convert it to

aeiou

How can I achieve it? (I don't need to compare, I need the new string to save)

5 Answers

Use the translate function:

SELECT TRANSLATE(
N'INPUT: ïÜ×ÌùµŪč©īĐÃÙěÓńÿâŘåòÔÕłćýçÀŻūìóèůüíÄûØõäÕťżîŃà£êřßøŽÖáďÉęúÂĪāËžŮōÑÇĆź®Š¥ĘĒśŹĚŚšŸ¢ŁéąÈđÆÍÛĄÝĎēČÊŌŇöÏňëÎæãŤñÒÚĀÅÁô',
N'ÁÀÂÃÄÅàáâãäåĀāąĄæÆÇçćĆčČ¢©đĐďĎÈÉÊËèéêëěĚĒēęĘÌÍÎÏìíîïĪīłŁ£ÑñňŇńŃÒÓÔÕÕÖØòóôõöøŌōřŘ®ŠšśŚßťŤÙÚÛÜùúûüůŮŪūµ×¥ŸÿýÝŽžżŻźŹ', 
N'aaaaaaaaaaaaaaaaaaccccccccddddeeeeeeeeeeeeeeiiiiiiiiiilllnnnnnooooooooooooooooorrsssssttuuuuuuuuuuuuuxyyyyyzzzzzz');

-- OUTPUT: 'INPUT: iuxiuuuccidaueonyaraooolcycazuioeuuiauooaotzioaleosozoadeeuaiaezuoncczrsyeeszessycleaedaiuaydeceonoineiaatnouaaao'

Check this link to find more 'look-a-like' characters:

https://github.com/apache/lucene-solr/blob/1ca7067a810578d4e246b5434b9cdcec7145d230/lucene/analysis/common/src/java/org/apache/lucene/analysis/miscellaneous/ASCIIFoldingFilter.java#L189

Sometimes, the string can have another COLLATION, so you still have accents in the result. In that case, you can use this line (based on this solution here):

SELECT convert(varchar, your_string) COLLATE SQL_Latin1_General_Cp1251_CS_AS;
Related