Why is the unicode star ignored?

In SQL Server, I run this simple query:

select iif(N'⭐' ='',1,0)

It returns 1. Why?

If I try other unicode charcters, all seems ok. i.e.:

select iif(N'✔️' ='',1,0)

returns 0 as expected.

Current Collation: Latin1_General_100_CI_AI

Solution

Some characters are ignored for comparison in certain collations. Emoticons are one such thing, where many will be seen as being equal to each other or nothing. For example CASE N'😺' WHEN N'🚗' THEN 1 END returns 1.

If you do need to deal with such characters, you are best off COLLATEing to a binary collation first, and then comparing.

SELECT 
       CASE N'😺' COLLATE Latin1_General_BIN WHEN N'🚗' THEN 1 END,
       CASE N'😺' COLLATE Latin1_General_BIN WHEN N'' THEN 1 END,
       CASE N'⭐' COLLATE Latin1_General_BIN WHEN N'' THEN 1 END;

Then returns NULL for all expressions.

db<>fiddle

It seems that the question asked and the problem to solve aren't actually the same. From the comments it seems that the OP wants a CONSTRAINT to CHECK that the value isn't ''. There are a few ways to achieve this which handle such characters.

You could COLLATE in the CHECK:

ALTER TABLE dbo.YourTable
ADD CONSTRAINT Chk_YourColumn
    CHECK (YourColumn COLLATE Latin1_General_BIN <> N'');

You could check the LEN, which would not allow values like ' ' either:

ALTER TABLE dbo.YourTable
ADD CONSTRAINT Chk_YourColumn CHECK (LEN(YourColumn) > 0);

You could check the DATALENGTH, which would allow values like ' ':

ALTER TABLE dbo.YourTable
ADD CONSTRAINT Chk_YourColumn CHECK (DATALENGTH(YourColumn) > 0);

Add a computed (and likely PERSISTED) column to the table and put the CONSTRAINT against that. This will be useful if you need to compare these characters in other places, and you'll be able to INDEX the column too:
```
ALTER TABLE dbo.YourTable
ADD YourCollatedColumn AS YourColumn COLLATE Latin1_General_BIN PERSISTED;
GO

ALTER TABLE dbo.YourTable
ADD CONSTRAINT Chk_YourCollatedColumn
    CHECK (YourCollatedColumn <> N'');
```