SQL Server is stripping out unicode in equals query

Question

Can anyone explain why when I run:

select * from (select N'someString💩' as id)_id where _id.id = N'someString'

in sql server I get a result of someString💩

I am not doing a like comparison, and cannot find anything in the documentation to explain this behaviour, I need an exact match or I need validation rules in my server to exclude any character that behaves like this.

DB is using collation SQL_Latin1_General_CP1_CI_AS if this has any impact?

Answer 1

Appears that, regardless of it's position, the poo emoji is skipped. For example the below returns every row:

SELECT *
FROM (VALUES(N'someString💩'),
            (N'💩someString'),
            (N'some💩String'),
            (N'some💩String💩')) V(S)
WHERE S = N'someString';

If you use a binary collation, this doesn't happen:

SELECT *
FROM (VALUES(N'someString💩'COLLATE SQL_Latin1_General_CP850_BIN),
            (N'💩someString'COLLATE SQL_Latin1_General_CP850_BIN),
            (N'some💩String'COLLATE SQL_Latin1_General_CP850_BIN),
            (N'some💩String💩'COLLATE SQL_Latin1_General_CP850_BIN)) V(S)
WHERE S = N'someString';

If SQL Server dealing with these unicode characters/emoji is important, then a binary collation will likely be your best choice.

Answer 2

You were probably in the right track. The collation will define how the comparison is made. Here's one option that could help you solve your problem.

SELECT *
FROM (VALUES(N'someString💩'),
            (N'💩someString'),
            (N'some💩String'),
            (N'some💩String💩'),
            (N'somestring')) V(S)
WHERE S = N'someString' COLLATE Latin1_General_100_CI_AI_KS_WS

Collation is described as Latin1-General-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive