How to remove accents and all chars a..z in sql-server?

Question

I need to do the following modifications to a varchar(20) field: - substitute accents with normal letters (like è to e) - after (1) remove all the chars not in a..z for example ``` 'aèàç=.32s df' ``` must become ``` 'aeacsdf' ``` are there special stored functions to achieve this easily? UPDATE: please provide a T-SQL not CLR solution. This is the workaround I temporarly did because it temporarly suits my needs, anyway using a more elegant approach would be better. ``` CREATE FUNCTION sf_RemoveExtraChars (@NAME nvarchar(50)) RETURNS nvarchar(50) AS BEGIN declare @TempString nvarchar(100) set @TempString = @NAME set @TempString = LOWER(@TempString) set @TempString = replace(@TempString,' ', '') set @TempString = replace(@TempString,'à', 'a') set @TempString = replace(@TempString,'è', 'e') set @TempString = replace(@TempString,'é', 'e') set @TempString = replace(@TempString,'ì', 'i') set @TempString = replace(@TempString,'ò', 'o') set @TempString = replace(@TempString,'ù', 'u') set @Tem…

Accepted Answer

You can avoid hard-coded `REPLACE` statements by using a `COLLATE` clause with an accent-insensitive collation to compare the accented alphabetic characters to non-alphabetic ones:

```
DECLARE 
  @s1 NVARCHAR(200),
  @s2 NVARCHAR(200)

SET @s1 = N'aèàç=.32s df'

SET @s2 = N''
SELECT @s2 = @s2 + no_accent 
FROM ( 
  SELECT 
    SUBSTRING(@s1, number, 1) AS accent,
    number
  FROM master.dbo.spt_values 
  WHERE TYPE = 'P'
  AND number BETWEEN 1 AND LEN(@s1) 
) s1 
INNER JOIN (
  SELECT NCHAR(number) AS no_accent
  FROM master.dbo.spt_values 
  WHERE type = 'P'
  AND (number BETWEEN 65 AND 90 OR number BETWEEN 97 AND 122) 
) s2 
  ON s1.accent COLLATE LATIN1_GENERAL_CS_AI = s2.no_accent 
ORDER BY number

SELECT @s1 
SELECT @s2

/*
aèàç=.32s df
aeacsdf
*/
```

How to remove accents and all chars <> a..z in sql-server?

Solution

Problem