1

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.

2
  • 0x00 is the null character. Jul 19, 2016 at 19:08
  • @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". Jul 19, 2016 at 23:43

1 Answer 1

3

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.

2
  • 1
    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.
    – Vaccano
    Jul 19, 2016 at 19:09
  • 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.
    – SQLDevDBA
    Jul 19, 2016 at 19:35

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.