SQL Query Where Column = '' returns Emoji 🎃 and 🍰

Question

So I have a table with three columns:

Id, Key, Value

I would like to delete all lines where Valueempty ( ''). So I wrote a query for selection before I delete:

Select * from [Imaging.ImageTag] where [Value] = ''

everything is pretty standard so far ...

Now this is the weird part. This query returned two lines, shown below, with comma delimited:

CE7C367C-5C4A-4531-9C8C-8F2A26B1B980,   ObjectType, 🎃 
F5B2F8A8-C4A8-4799-8824-E5FFEEDAB887,   Caption,    🍰

Why do these two lines correspond ''?

additional information

I use Sql-Server, the column [Value]is of type NVARCHAR(300)and yes, the table name is really[Imaging.ImageTag]

Answer 1

It depends on the sort.

Matches an empty string

SELECT 1 where N'' = N'🍰'  COLLATE latin1_general_ci_as

Doesn't match an empty string

SELECT 1 WHERE N'' = N'🍰'   COLLATE latin1_general_100_ci_as

100 ( , 2008 ), , . BOL 100

, .

Answer 2

"", , , :

Select * from [Imaging.ImageTag] where LEN([Value]) = 0

( / ):

SELECT CASE WHEN N'' = N'🍰' then 1 else 0 end --returns 1, no good for checking

SELECT LEN(N'🍰') --returns 2, can be used to check for zero length values?

Answer 3

Google varchar. , - :

SELECT mycolumn
FROM mytable
WHERE REGEXP_EXTRACT(mycolumn,'\x{1f600}')  <> ''

\x{1f600}- this is a character code for emoticons, you can find emoticon codes here.