Home SQL Query Where Column = '' returning Emoji characters 🎃 and 🍰
Reply: 2

SQL Query Where Column = '' returning Emoji characters 🎃 and 🍰

user1
1#
user1 Published in 2017-11-29 11:17:15Z

Ok so I have a table with three columns:

Id, Key, Value

I would like to delete all rows where Value is empty (''). Therefore I wrote the query to select before I delete which was:

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

all pretty standard so far...

Now heres the strange part. This query returned two rows shown below with commas seperating columns:

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

Why are these two rows matching on ''?

Extra Info

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

Martin Smith
2#
Martin Smith Reply to 2017-11-29 20:07:28Z

This is collation dependant.

Matches empty string

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

Doesn't match empty string

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

The 100 collations are more up-to-date (though still not bleeding edge, they have been available since 2008) and you should use more modern collations unless you have some specific reason not to. The BOL entry for 100 collations specifically calls out

Weighting has been added to previously non-weighted characters that would have compared equally.

Caius Jard
3#
Caius Jard Reply to 2017-11-29 11:26:07Z

It's not an answer to your "why", but in terms of your overall goal, perhaps you should alter your strategy for searching for empty values:

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

As per the comments (thanks Martin Smith for providing some copy/pastable emoji):

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?
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.388598 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO