0

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?

Improve this question
1
  • @mark_s what is the difference between SQL Server and MSSQL? I usually use MSSQL notation when searching as SQL Server is too generic.
    – Stefan Rogin
    Commented Oct 29, 2013 at 13:23

1 Answer 1

Reset to default
5

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'
Improve this answer
1
  • 2
    Seems to be something fixed in the more recent collations. Latin1_General_100_CI_AS returns 'IAxI'
    – Martin Smith
    Commented Oct 29, 2013 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.