[SOLVED] Convert ANSI to UTF in T-SQL, replacing German Umlaut 'ß' results in replacement of 'ss' as well

Convert ANSI to UTF in T-SQL, replacing German Umlaut 'ß' results in replacement of 'ss' as well

I'm trying to convert ANSI strings to UTF for the use in JSON format for HTTP requests.

There is a specialty in German with the Umlaut 'ß'. When special keys are not allowed, 'ß' is written 'ss'. T-SQL seems to follow this rule too eagerly ;-)

To convert German "Umlaute" to UTF ESC-sequences, I have written a function to replace chars.

SELECT REPLACE('E**ss**ener Straße','ß','\u00DF')

=> 'E**\u00DF**er Stra\u00DFe'

What I need as result is 'Essener Stra\u00DFe'

MS Copilot hinted me to use N in front of strings like

SELECT REPLACE(N'Essener Straße',N'ß','\u00DF')

But unfortunately, it doesn't help.

I am using SQL Server Standard (64-bit) version 15.0.2000.5

Solution

Binary collation seems to do what is needed

SELECT N'Essener Straße' as original
, REPLACE(N'Essener Straße' collate LATIN1_GENERAL_BIN, N'ß' collate LATIN1_GENERAL_BIN, '\u00DF') as modified

returns Essener Stra\u00DFe

https://sqlfiddle.com/sql-server/online-compiler?id=e178647f-44ea-4e80-a1e0-0335f6d107f5