Multibyte replace not working in SQL Server

Question

Why does one work and the other doesn't?

SELECT REPLACE(N'IAșI' ,N'ș','x') -- doesn't match (returns 'IAșI')
SELECT REPLACE('IAșI' ,'ș','x') -- works (returns 'IAxI')

My guess would be that in the second example SQL Server sees 'ș' as 2 characters (0x19 02) and doesn't do some collation optimisations that ruin the multibyte match.

What should be done for the multibyte example to work?

Answer 1

I can't explain why the first one isn't working (perhaps some subtlety of that particular Unicode character). This produces a rather unexpected result:

SELECT REPLACE(N'IAșI', N'șI', 'x') -- returns 'xAșx'

The second one will appear to work because SQL Server replaces multi-byte characters with '?' when casting down to varchar.

This small example demonstrates the behavior, as all arguments will be converted to varchar literals:

SELECT 'IAșI', REPLACE('IA?I', 'ș', 'x'), REPLACE('IAșI', '?', 'x') -- returns 'IAxI'

EDIT: Seems to be collation related.

SELECT REPLACE(N'IAșI' COLLATE Latin1_General_CI_AS, N'ș', 'x') -- doesn't match (returns 'IAșI')
SELECT REPLACE(N'IAșI' COLLATE Latin1_General_Bin, N'ș', 'x') -- returns 'IAxI'