I don't understand why the following returns 'Equal':
SELECT IIF(N'✅' COLLATE Latin1_General_100_CI_AI_SC = N'' COLLATE Latin1_General_100_CI_AI_SC, 'Equal', 'NotEqual')
returns Equal
I am aware that sql server ignores trailing spaces when comparing strings however I don't understand this result.
Even more bizarre, if I use a different emoji, the result is 'NotEqual':
SELECT IIF(N'🙂' COLLATE Latin1_General_100_CI_AI_SC = N'' COLLATE Latin1_General_100_CI_AI_SC, 'Equal', 'NotEqual')
returns NotEqual
Edit: I'm aware of https://stackoverflow.com/questions/47551528/sql-query-where-column-returning-emoji-characters-and and the fact that the behavior is expected on older, non-100 collations. However I am using a 100 collation here.
'🙂'
, it is not considered equivalent to the empty string. The collationLatin1_General_100_CI_AI_SC
does not define an equivalence between'🙂'
and an empty''
string. so, the comparison results in 'NotEqual'.✅
in that collation. TryLatin1_General_100_BIN2