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.
Best Answer
Run this query:
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.