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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.