1

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.

3
  • 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
    Commented May 8, 2023 at 1:33
  • What equivalence are you talking about?
    – Clement
    Commented May 8, 2023 at 2:31
  • Relevant stackoverflow.com/a/60222376/14868997 there is no weight assigned for in that collation. Try Latin1_General_100_BIN2 Commented May 8, 2023 at 12:56

0

Your Answer

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

Browse other questions tagged or ask your own question.