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.

  • when you use a different emoji, such as '🙂', it is not considered equivalent to the empty string. The collation Latin1_General_100_CI_AI_SC does not define an equivalence between '🙂' and an empty '' string. so, the comparison results in 'NotEqual'.
    – Kin Shah
    May 8 at 1:33
  • What equivalence are you talking about?
    – Clement
    May 8 at 2:31
  • Relevant stackoverflow.com/a/60222376/14868997 there is no weight assigned for in that collation. Try Latin1_General_100_BIN2 May 8 at 12:56


Your Answer

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

Browse other questions tagged or ask your own question.