9

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]

Improve this question
8
  • 2
    What database platform are you using & what it the exact type of Value? Commented Nov 29, 2017 at 11:18
  • Can you do something like this: Select *, CAST([value] as VARBINARY) from [Imaging.ImageTag] i where [Value] = '' (SQLServer notation..) and show us? I suspect that whatever bytes that make up the emoji are naively being treated as equal to an emtpy string by the compare, perhaps because they start with an ascii nul 0x00.. (perhaps the emoji is being converted to ascii to compare, and teh conversion is reducing it to '') Commented Nov 29, 2017 at 11:21
  • In SQL Server at my default collation SELECT 1 where '' = N'🍰' returns 1 - so it does match empty string for some reason. Commented Nov 29, 2017 at 11:21
  • ...and are these Emojis actually a bad thing? I wish my queries at work were this colorful ^ ^ Commented Nov 29, 2017 at 11:22
  • [Imaging.ImageTag] looks wrong. Do you really have a table with the name "Imaging.ImageTag"? Commented Nov 29, 2017 at 11:23

4 Answers 4

Reset to default
14

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

@martin_smith Very interresting. Can you explain a bit about what the collate is actually doing? and if there a way to set this at a database level rather than query level?
The collation sets the string comparison semantics. You can set it at database and column level as well as query level but setting it at database level won't cascade down to existing columns. If this is an existing application I wouldn't change it at database level unless you have good test coverage as you may find you end up getting collation mismatch errors for comparisons.
@user1 for more info, please see: How Many Bytes Per Character in SQL Server: a Completely Complete Guide
2

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?
Improve this answer

Comments

0

Complementing this answers When you need use 'like' at sql

WHERE
N'' + COLUMNS like N'%'+ @WordSearch +'%' COLLATE latin1_general_100_ci_as
Improve this answer

Comments

-1

Google send me here looking for a way filter all rows with an emoji on a varchar column. In case that your looking for something similar:

SELECT mycolumn
FROM mytable
WHERE REGEXP_EXTRACT(mycolumn,'\x{1f600}')  <> ''
--sqlserver WHERE SUBSTRING(MyCol, (PATINDEX( '\x{1f600}', MyCol ))) <> ''

the \x{1f600} is the char code for the searched emoji, you can find the emoji codes here

Improve this answer

2 Comments

I don't think SQL Server (from the OP) has REGEXP_EXTRACT()
In SQL server you can use SUBSTRING(MyCol, (PATINDEX( <regex>, [MyCol] ))).

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.