Replacing Foreign Chars replaces 'ss' instead of 'ß' in SQL Function

Viewed 70

I wrote myself a quick function to fix and/or replace foreign characters.

For some reason it sees 'ss' as 'ß' and incorrectly replaces it. What is going on? How can I ensure that only the 'ß' is getting replaced?

ALTER FUNCTION [dbo].[fn_ReplaceForeignChars](
    @mode int,
    @inStr nvarchar(max) ) 
    RETURNS nvarchar(max) 
AS 
BEGIN
    
    DECLARE @outStr nvarchar(max)

    SET @outStr = @inStr

    SET @outStr = REPLACE(@outStr, 'ü', 'ü')
    SET @outStr = REPLACE(@outStr, 'Ü', 'Ü')
    SET @outStr = REPLACE(@outStr, 'ö', 'ö')
    SET @outStr = REPLACE(@outStr, 'ß', 'ß')
    SET @outStr = REPLACE(@outStr, 'é', 'é')
    SET @outStr = REPLACE(@outStr, 'ø', 'ø')
    SET @outStr = REPLACE(@outStr, 'è', 'é')
    SET @outStr = REPLACE(@outStr, 'ë', 'ë')
    SET @outStr = REPLACE(@outStr, 'ô', 'ô')
    SET @outStr = REPLACE(@outStr, 'Ã…', 'Å')
    SET @outStr = REPLACE(@outStr, 'ä', 'ä')
    SET @outStr = REPLACE(@outStr, 'Ã¥', 'å')
    SET @outStr = REPLACE(@outStr, 'ó', 'ó')
    SET @outStr = REPLACE(@outStr, 'ú', 'ú')
    SET @outStr = REPLACE(@outStr, 'á', 'á')
    SET @outStr = REPLACE(@outStr, 'É', 'É')
    SET @outStr = REPLACE(@outStr, 'Ö', 'Ö')
    SET @outStr = REPLACE(@outStr, 'í', 'í')--  (Hidden char next to Ã)
    SET @outStr = REPLACE(@outStr, 'ñ', 'ñ')
    SET @outStr = REPLACE(@outStr, 'ò', 'ò')
    SET @outStr = REPLACE(@outStr, 'Ä', 'Ä')



    IF (@mode = 1)
    BEGIN
        SET @outStr = REPLACE(@outStr, 'ü', 'u')
        SET @outStr = REPLACE(@outStr, 'ú', 'u')
        SET @outStr = REPLACE(@outStr, 'Ü', 'U')

        SET @outStr = REPLACE(@outStr, 'ö', 'o')
        SET @outStr = REPLACE(@outStr, 'ô', 'o')
        SET @outStr = REPLACE(@outStr, 'ó', 'o')
        SET @outStr = REPLACE(@outStr, 'ò', 'o')
        SET @outStr = REPLACE(@outStr, 'ø', 'o')
        SET @outStr = REPLACE(@outStr, 'Ö', 'O')

        SET @outStr = REPLACE(@outStr, 'ß', 'B')

        SET @outStr = REPLACE(@outStr, 'é', 'e')
        SET @outStr = REPLACE(@outStr, 'é', 'e')
        SET @outStr = REPLACE(@outStr, 'ë', 'e')
        SET @outStr = REPLACE(@outStr, 'É', 'E')

        SET @outStr = REPLACE(@outStr, 'Å', 'A')
        SET @outStr = REPLACE(@outStr, 'Ä', 'A')
        SET @outStr = REPLACE(@outStr, 'ä', 'a')
        SET @outStr = REPLACE(@outStr, 'å', 'a')
        SET @outStr = REPLACE(@outStr, 'á', 'a')
        
        SET @outStr = REPLACE(@outStr, 'í', 'i')
        SET @outStr = REPLACE(@outStr, 'ñ', 'n')
    END

    RETURN @outStr
END
1 Answers

What is going on?

The current collation considers 'ss' and 'ß' to be equivilent.

You can run this in a database with a binary collation, or specify the collation in the expression like this:

DECLARE @outStr nvarchar(max) = 'floss' 

SET @outStr = REPLACE(@outStr, 'ß' collate Latin1_General_100_BIN2, 'B') 

select @outStr
Related