SDU Tools: Strip diacritics from strings in SQL Server T-SQL

I try to allow strings in my applications to use all the richness of the available character sets. If someone has an accent in their name, I really want to repeat it back that way. But I’ve been asked many times if there’s a simple way to strip out all the accents, graves, etc. in T-SQL. These are called diacritics, and we’ve created a function to help you remove them if you decide you really must. (But as I said, really consider keeping them).

The StripDiacritics function in our free SDU Tools for developers and DBAs, does this. It provides the separator that’s currently in use.

It takes a single parameter: the input string.

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

https://youtu.be/Aqiqa9OXNqQ

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the current code is backwards compatible to SQL Server 2008 and later. Once our minimum code level is much higher, we’ll replace this code with a version using the T-SQL TRANSLATE function.

Note: the code might wrap when displayed below.

CREATE OR ALTER FUNCTION SDU_Tools.StripDiacritics
(
    @InputString nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN

-- Function:      Strips diacritics (accents, graves, etc.) from a string
-- Parameters:    @InputString nvarchar(max) - string to strip
-- Action:        Strips diacritics (accents, graves, etc.) from a string
-- Return:        nvarchar(max)
-- Refer to this video: https://youtu.be/Aqiqa9OXNqQ
--
-- Test examples: 
/*

SELECT SDU_Tools.StripDiacritics(N'śŚßťŤÄÅàá');

*/

    DECLARE @CharactersToReplace nvarchar(max) 
        = N'ÁÀÂÃÄÅàáâãäåĀāąĄæÆÇçćĆčČ¢©đĐďĎÈÉÊËèéêëěĚĒēęĘÌÍÎÏìíîïĪīłŁ£'
        + N'ÑñňŇńŃÒÓÔÕÕÖØòóôõöøŌōřŘ®ŠšśŚßťŤÙÚÛÜùúûüůŮŪūµ×¥ŸÿýÝŽžżŻźŹ';
    DECLARE @ReplacementCharacters nvarchar(max)
        = N'aaaaaaaaaaaaaaaaaaccccccccddddeeeeeeeeeeeeeeiiiiiiiiiilll'
        + N'nnnnnooooooooooooooooorrsssssttuuuuuuuuuuuuuxyyyyyzzzzzz';
    
    DECLARE @Counter int = 1;
    DECLARE @ReturnValue nvarchar(max) = @InputString;
    
    -- Replace loop with TRANSLATE when lowest supported version = 2016
    WHILE @Counter <= LEN(@CharactersToReplace)
    BEGIN
        SET @ReturnValue = REPLACE(@ReturnValue, 
                                   SUBSTRING(@CharactersToReplace, @Counter, 1),
                                   SUBSTRING(@replacementCharacters, @Counter, 1));
        SET @Counter = @Counter + 1;
    END;
    
    RETURN @ReturnValue;
END;
GO

2021-01-07