Can REPLACE() be made accent insensitive (é = e)?

Question

I have a table which uses UTF-8- default collation.

I have a column in this table called company which has this value Café Rouge

When I do the query like:

 select * from company where name ='Cafe Rouge'

it lists this company because it treats the character é =e but when I use the replace command, it does not treat é as e.

So,

select replace('Café Rouge','e','z')  

gives me 'Café Rougz' i.e. it does not replace é with z.

Any suggestions will be appreciable

Answer 1

I think among the good things you can do is maintain a second column which keeps these strings in a normal form; for example, this column would hold "Cafe Rouge" rather than "Café Rouge". You can project the exceptional characters in your query and then use the normal form column to query against.

Answer 2

Try

SELECT REPLACE(CONVERT(name USING ascii), 'e', 'z')

to convert the name column to ASCII before doing the replace.