Matching empty string is not working

Question

I have the following query

SELECT Ice.IceId, Ice.Code, Box.Ice, LEN(Box.Ice) AS IceCharLength,
 DATALENGTH(Box.Ice) AS DataLength, Box.Ice, ASCII(Box.Ice) AS ASCII, 
 CAST(Box.ice AS VARBINARY) AS VarBinary
FROM dbo.tblESPShipBox Box
LEFT JOIN ReferenceManual.IceType Ice
 ON Ice.Code = CASE
 WHEN Box.ICE IS NULL THEN 'N'
 WHEN RTRIM(Box.ICE) = '' THEN 'N'
 WHEN Box.Ice IN ('', ' ', '/') THEN 'N'
 ELSE Box.ICE
 END
WHERE BOX_SEQ_NUM = '000023' AND BOX_TYPE_ID = 0

It returns the following results:

IceId Code Ice IceCharLength DataLength Ice ASCII VarBinary
----------- ---- ---- ------------- ----------- ---- ----------- -------------
2 N 0 1 32 0x20
NULL NULL 1 1 0 0x00

The weird thing is that Box.ICE for the first row is ' ' (Single Space String). The second row seems to be '' (No space string). (The way I see this is by copying the cell out and pasting in into other text to see if it makes a space or not.)

The Box.ICE column is a char(1) so I don't really see how a '' (no space string) got in there.

But the weird part is that my case statement should still match on a '' (no space) char. But clearly it does not.

How can I figure out what is really in this field and make it match my case statement?

Things I have tried:

• Used SSMSBoost to do a special char included copy of the cell into NotePad++ (with show all chars on). I did this to make sure there are no unplayable unicode chars.

• Martin Smith: 0x00 is the null character.
• Solomon Rutzky: @MartinSmith and Vaccano: regarding "shouldn't it still match an empty string?", it doesn't only because it's VARCHAR data using a SQL Server Collation, probably SQL_Latin1_General_CP1_CI_AS. If you were using NVARCHAR or a Windows Collation it would match the empty string branch of the CASE statement. Run the following 3 queries to verify this behavior: SELECT 1 WHERE CHAR(0x00) = '' COLLATE Latin1_General_100_CI_AS; SELECT 2 WHERE CHAR(0x00) = '' COLLATE SQL_Latin1_General_CP1_CI_AS; SELECT 3 WHERE NCHAR(0x00) = '' COLLATE SQL_Latin1_General_CP1_CI_AS; They will return "1" and "3".

Answer 1

Run this query:

 SELECT ASCII(' ') AS [SingleSpaceString]
 , ASCII('') AS [NoSpaceString]

You will note the the Value for [SingleSpaceString] is '32', while [NoSpaceString] is NULL.

I'd suggest adding ASCII(Box.Ice) to your select statement to confirm that a true space is the value, vs. an odd character.

• Vaccano: I updated the case statement to have WHEN ASCII(Box.Ice) = 0 THEN 'N' and that got the match I needed. Still seems odd to me though.
• SQLDevDBA: I'd also suggest adding the CASE statement you have into your SELECT statement so that you're able to see what the JOIN is working with. That might bring a few things to light for you. Maybe change your THEN statements to THEN 'Then1', THEN 'Then2' and THEN 'Then3', and also your "ELSE Box.Ice" to : "ELSE 'ElseReached' and see if you can track it down and confirm which CASE is being reached.