ISNULL not working on one row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

ISNULL not working on one row

Author

Topic

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-05 : 10:59:17

I must be missing something. I have the following code that formats a data row into a | separated string. It is checking for NULLs but on one row it seems to ignoring that fact. The underlined lines were added for debugging proposes.


SET NOCOUNT ON

   SELECT --DISTINCT
          --TOP 500000
          ISNULL(CONVERT(VARCHAR(50), CUST_EDP), '')  + '|' +
          CONVERT(VARCHAR(255), RTRIM(LTRIM(ISNULL([ADDRESS1], '')))) + '|' +
          CONVERT(VARCHAR(255), RTRIM(LTRIM(ISNULL([ADDRESS2], '')))) + '|' +
          CONVERT(VARCHAR(255), RTRIM(LTRIM(ISNULL([CITY], '')))) + '|' +
          CONVERT(VARCHAR(255), RTRIM(LTRIM(ISNULL([STATE_CD], '')))) + '|' +
          CONVERT(VARCHAR(255), RTRIM(LTRIM(ISNULL([POSTAL_CD], '')))) + '|' +
          [USCAN_CD] AS Formatted_String
     , LEN(CONVERT(VARCHAR(255), RTRIM(LTRIM(ISNULL([ADDRESS1], ''))))) AS LENAddress1   
     , CONVERT(VARBINARY, [ADDRESS1]) AS BinAddress1  
     FROM [NameSearch_Customer_BaseList_RecordsToProcess]
    WHERE USCAN_CD IN ('USA', 'CAN')
   AND ISNULL([ADDRESS1], '') <> '' 
   AND ISNULL([CITY], '') <> '' 
   AND ISNULL([STATE_CD], '') <> '' 
   AND ISNULL([POSTAL_CD], '') <> '' 
      AND CUST_EDP IN (2328587, 2204406)
    ORDER BY USCAN_CD 
    
SET NOCOUNT OFF

The output is shown below. It seems that even though there are no character values for ADDRESS1 the second line a) still shows a length of 30 and b) is not responding to the ISNULL directive.

EDIT: It appears I have 12 rows with this situation in my table.


Formatted_String	LENAddress1	BinAddress1
2204406|150 E 69TH ST APT 3P||NEW YORK|NY|100215722|USA	20	0x313530204520363954482053542041505420335020202020202020202020
2328587|                              |782|6611 E 129TH AVE|TH|CO|USA	30	0x000000000000000000000000000000000000000000000000000000000000

Does anyone have any ideas as to what's going on here?

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-05 : 12:14:59

Your data is not null. e.g.

select convert(varbinary, null)

produces:

(No column name)
NULL

but you get 0x000..... instead

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-05 : 12:38:28

Okay. Well then I guess my Band-Aid fix of converting to varbinary and checking for that value will have to suffice.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources