sql server - How can I remove accents on a string?

Question

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)

Answer 1

Try using COLLATE:

select 'áéíóú' collate SQL_Latin1_General_Cp1251_CS_AS

For Unicode data, try the following:

select cast(N'áéíóú' as varchar(max)) collate SQL_Latin1_General_Cp1251_CS_AS

I am not sure what you may lose in the translation when using the second approach.

Update

It looks like ? is a special case, and we have to handle upper and lower case separately. You can do it like this (this code is a good candidate for a user-defined function):

declare @str nvarchar(max) = N'?aàeéêèio?; ?uf un ?uf'
select cast(
    replace((
        replace(@str collate Latin1_General_CS_AS, '?' collate Latin1_General_CS_AS, 'OE' collate Latin1_General_CS_AS) 
    ) collate Latin1_General_CS_AS, '?' collate Latin1_General_CS_AS, 'oe' collate Latin1_General_CS_AS) as varchar(max)
) collate SQL_Latin1_General_Cp1251_CS_AS 
-- Output:
-- naaeeeeioo; Oeuf un oeuf

User Defined Function

create function dbo.fnRemoveAccents(@str nvarchar(max))  
returns varchar(max) as
begin
return cast(
    replace((
        replace(@str collate Latin1_General_CS_AS, '?' collate Latin1_General_CS_AS, 'OE' collate Latin1_General_CS_AS) 
    ) collate Latin1_General_CS_AS, '?' collate Latin1_General_CS_AS, 'oe' collate Latin1_General_CS_AS) as varchar(max)
) collate SQL_Latin1_General_Cp1251_CS_AS 
end