Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
@mark_s what is the difference between SQL Server and MSSQL? I usually use MSSQL notation when searching as SQL Server is too generic. –  clickstefan Oct 29 '13 at 13:23
add comment

1 Answer

up vote 4 down vote accepted

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'
share|improve this answer
2  
Seems to be something fixed in the more recent collations. Latin1_General_100_CI_AS returns 'IAxI' –  Martin Smith Oct 29 '13 at 13:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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