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

user_193655 0
Viewed 110177

I need to do the following modifications to a varchar(20) field:

  1. substitute accents with normal letters (like è to e)
  2. 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 @TempString =  replace(@TempString,'ç', 'c')
  set @TempString =  replace(@TempString,'''', '')
  set @TempString =  replace(@TempString,'`', '')
  set @TempString =  replace(@TempString,'-', '')
  return @TempString
END
GO
sql-server-2005 tsql sql-server
14 Answers

I was writing this answer for another question, but then the OP deleted the question just as I went to post it... So I'll post this here, as it's related. This doesn't use a WHILE or a Multi-Line Scalar Function (as is in the most upvoted answer), and I've provided an nvarchar and varchar solution:

nvarchar:

CREATE FUNCTION dbo.RemoveAccents_N (@String nvarchar(4000)) 
RETURNS table
AS RETURN

    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT TOP (LEN(@String)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3, N N4)
    SELECT (SELECT CASE WHEN V.CS LIKE '[A-z]' THEN V.CS ELSE V.YS END
            FROM Tally T
                 CROSS APPLY (VALUES(SUBSTRING(@String,T.I,1), CONVERT(varchar(4000),SUBSTRING(@String,T.I,1)) COLLATE SQL_Latin1_General_CP1253_CI_AI))V(YS,CS)
            FOR XML PATH(N''),TYPE).value('.','nvarchar(4000)') AS AccentlessString;

varchar:

CREATE FUNCTION dbo.RemoveAccents (@String varchar(8000)) 
RETURNS table
AS RETURN

    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT TOP (LEN(@String)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3, N N4)
    SELECT (SELECT CASE WHEN V.CS LIKE '[A-z]' THEN V.CS ELSE V.YS END
            FROM Tally T
                 CROSS APPLY (VALUES(SUBSTRING(@String,T.I,1), SUBSTRING(@String,T.I,1) COLLATE SQL_Latin1_General_CP1253_CI_AI))V(YS,CS)
            FOR XML PATH(N''),TYPE).value('.','varchar(8000)') AS AccentlessString;

The functions can be called as below:

SELECT *
FROM (VALUES(N'Åìèë Öàíêîâ @11 ώЙ⅔♠♪'))V(YourString)
     CROSS APPLY dbo.RemoveAccents_N(V.YourString) RA;
user_20299830

Simplified with:

CREATE OR ALTER FUNCTION F_CLEAN_ACCENT_STRING(@NAME NVARCHAR(128))
RETURNS NVARCHAR(128)
AS
BEGIN
   DECLARE @OUT NVARCHAR(128) = '', @I TINYINT = 1, @C CHAR(1);
   WHILE @I <= LEN(@NAME)
   BEGIN
      IF @C NOT LIKE '[A-Z0-9_]' COLLATE French_CI_AI
         SET @OUT = @OUT + '_';
      ELSE 
         SET @OUT = @OUT + CHAR(ASCII(SUBSTRING(@NAME, @I, 1) COLLATE SQL_Latin1_General_CP1253_CI_AI));
      SET @I += 1;
   END;
   RETURN @OUT;
END;
GO

SELECT dbo.F_CLEAN_ACCENT_STRING('Électricîté de Fränce') AS UNACCENT;

UNACCENT
----------------------
Electricite de France
user_126598720

In mySql as a function (converting + other stuff)

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `txt2slug`(`txt` VARCHAR(50)) RETURNS varchar(50) CHARSET utf8mb4
BEGIN   
    DECLARE pl_char VARCHAR(10) DEFAULT "ęóąśłżźćń";
    DECLARE en_char VARCHAR(10) DEFAULT "eoaslzzcn";
    DECLARE slug VARCHAR(50) DEFAULT (txt); 
    DECLARE pos INT DEFAULT 1;  
        
    SET slug = LOWER(TRIM(slug));   
    SET slug = REGEXP_REPLACE(slug, "[,;:!?.]", "");    
    SET slug = REGEXP_REPLACE(slug, " ", "-");  
        
    WHILE pos <= LENGTH(pl_char) DO     
        SET slug = REGEXP_REPLACE(slug, SUBSTRING(pl_char, pos, 1), SUBSTRING(en_char, pos, 1));
        SET pos = pos + 1;
    END WHILE;  
        
    RETURN slug;    
    
END$$
DELIMITER ;
user_125012840
Related