Replace accent with upper and lowercase

Question

Have a table of customers with accented letters, I need to replace them with the same letter without the accent, I can use the REPLACE feature, but my problem is that I have some clients in upper case and some in lower case and I need keep uppercase and lowercase

Possible duplicate of http://stackoverflow.com/questions/4024072/how-to-remove-accents-and-all-chars-a-z-in-sql-server — Bajal, Jan 05 '16 at 18:31
If you need the upper case, replace them for upper case equivalents. Do the same for the lowercase. — Kamil Gosciminski, Jan 05 '16 at 23:10
Ok Consider Me, like this: "UPDATE YourTable SET col = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(col COLLATE Latin1_General_CI_AI ,'á','a'),'Á','A'),'é','e'),'Ã','A'),'ê','e')" — Mike Tex, Jan 05 '16 at 23:24
This may be relevant: https://www.mssqltips.com/sqlservertip/1032/case-sensitive-search-on-a-case-insensitive-sql-server/ — Andrew Savinykh, Jan 06 '16 at 00:32
@MikeTex I've included my answer. It needs a change of collation for your column. Try it out and reply back under my answer if this works for all of your diacritics. — Kamil Gosciminski, Jan 06 '16 at 01:04
@MikeTex could I shed some light into it? Is the solution proposed not working? — Kamil Gosciminski, Jan 07 '16 at 21:58

Kamil Gosciminski · Answer 1 · 2016-01-06T01:13:51.453

As suggested in comments, your expected result could be achieved using REPLACE() function.

If you need to preserve upper and lower case letters, then write explicitly replace functions for both cases AND change the collation to case-sensitive one: Latin1_General_CS_AS.

Here is the related post on using this collation for case sensitive search. and a quote from it:

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Using your example characters from comments, that are in the input below it seems to be working.

Query

SELECT REPLACE(REPLACE(REPLACE(REPLACE('áÁéÃ' COLLATE Latin1_General_CS_AS, 'á','a'),'Á','A'),'é','e'),'Ã','A')

Input: áÁéÃ

Output: aAeA

Check SQL Fiddle to view the example in action.

Replace accent with upper and lowercase

1 Answers1