Why does 'œ' match 'oe' in an NVarchar but not in a Varchar

Question

SELECT REPLACE(N'Chloe', 'œ', 'o'), REPLACE('Chloe', 'œ', 'o')

Results in:

Chlo    Chloe

This is super weird.

---

Another way:

SELECT
  CASE WHEN N'œ' = N'oe' THEN 1 ELSE 0 END as NVarcharMatch,
  CASE WHEN 'œ' = 'oe' THEN 1 ELSE 0 END as VarcharMatch

Results in:

NVarCharMatch   VarcharMatch
1   0

Answer 1

Both legacy SQL collations ("SQL" collation prefix) and binary collations ("BIN" prefix compare only single characters at a time so "œ" can never equal "oe".

Windows collations and Unicode comparison use more robust comparison rules. This allows the single "œ" character to compare as equal to the 2 consecutive characters "oe" because they are semantically identical.

--Chlo because Unicode comparison equal
SELECT REPLACE(N'Chloe' COLLATE SQL_Latin1_General_CP1_CI_AS, 'œ', 'o'); 

--Chloe because legacy SQL comparison unequal
SELECT REPLACE('Chloe' COLLATE SQL_Latin1_General_CP1_CI_AS, 'œ', 'o'); 

--Chloe because binary comparison unequal
SELECT REPLACE('Chloe' COLLATE Latin1_General_BIN, 'œ', 'o');

--Chlo because Windows collation comparison equal
SELECT REPLACE('Chloe' COLLATE Latin1_General_CI_AS, 'œ', 'o');