How to remove accents in a string easily

In some needs you have to remove accents in a string. If this is a case here is a small tip about how to remove them !

After Microsoft :

Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types, such as char and varchar, dictate the code page and corresponding characters that can be represented for that data type.

So basically, if you find a collation that doesn’t have accents and you force your string in that one, it will remove the accents.

To force it, we will use the COLLATE statement.

Here is an example :

DECLARE @String varchar(50) = 'éàèÿ'
SELECT @String COLLATE SQL_Latin1_General_Cp1251_CS_AS

The result of that query will return “eaey”

If wanted, you can obviously put the result in a variable

DECLARE @StringWithAccents varchar(50) = 'éàèÿ'
DECLARE @StringWithoutAccents varchar(50) 

SET @StringWithoutAccents = @StringWithAccents COLLATE  SQL_Latin1_General_Cp1251_CS_AS  

SELECT @StringWithAccents AS WithAccents
SELECT @StringWithoutAccents AS WithoutAccents

It is as easy that it seems !

Microsoft docs on collation : https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support

COLLATE statement : https://docs.microsoft.com/en-us/sql/t-sql/statements/collations

Tags: accentscollatecollationsqlSQLCollationtsql