How can I remove accents on a string?
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)
Solution 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
Solution 2:
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;