RE: Is hex 00000 in a string field equal to null?

Mike,

COALESCE and IFNULL only work against the null value (i.e. no data) for a
field. Having the null character (X'00') means there is actual data there.

Try this:
CASE
WHEN SUBSTR(SUPV_COMMENTS,1,1) = X'00' THEN ' '
ELSE SUPV_COMMENTS
END

That should convert your comments to blanks if the initial character is
null. Otherwise it will return the actual comments.

Have fun!
Richard

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike Wills
Sent: Tuesday, December 06, 2011 3:51 PM
To: Midrange Systems Technical Discussion
Subject: Re: Is hex 00000 in a string field equal to null?

So, how I can bring that back to *blanks in SQL?

HEX(COALESCE(SUPV_COMMENTS, ' ')) and HEX(IFNULL(SUPV_COMMENTS, ' '))

Both don't seem to give me the expected result.

--
Mike Wills
http://mikewills.me