Skip to main content
Database Administrators

Return to Question

added 215 characters in body
Source Link
Vaccano
Vaccano
  • 2.5k
  • 5
  • 31
  • 56
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.BoxtblESPShipBox 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

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

SELECT Ice.IceId, Ice.Code, Box.Ice, LEN(Box.Ice) AS IceCharLength,
       DATALENGTH(Box.Ice) AS DataLength
FROM dbo.Box 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

IceId       Code  Ice   IceCharLength DataLength
----------- ----  ----  ------------- -----------
2           N           0             1
NULL        NULL        1             1

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

IceId       Code Ice  IceCharLength DataLength  Ice  ASCII       VarBinary
----------- ---- ---- ------------- ----------- ---- ----------- -------------
2           N         0             1                32          0x20
NULL        NULL      1             1                0           0x00
Source Link
Vaccano
Vaccano
  • 2.5k
  • 5
  • 31
  • 56

Matching empty string is not working

I have the following query

SELECT Ice.IceId, Ice.Code, Box.Ice, LEN(Box.Ice) AS IceCharLength,
       DATALENGTH(Box.Ice) AS DataLength
FROM dbo.Box 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
----------- ----  ----  ------------- -----------
2           N           0             1
NULL        NULL        1             1

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.