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

Thanks! The bug showed up again today, so I tried the replace. It worked
perfectly! Now to figure out the damn bug. I am sure it has to do with the
36 character limit on RPG parameters. I'll probably remove the RPG from the
picture to see if that resolves it.

--
Mike Wills
http://mikewills.me


On Fri, Dec 9, 2011 at 10:32 AM, Mark Murphy/STAR BASE Consulting Inc. <
mmurphy@xxxxxxxxxxxxxxx> wrote:

no need to go through all those gyrations, a single built-in will do the
job

REPLACE(field, x'00', ' ')

will do the trick.

It is easy for RPG programers to miss because the %REPLACE function is
different from all other REPLACE built in's that I have come across.
Usually replace will find a pattern within a string, and replace it with
another string, wherever it occurs. It is handy for compressing out
unwanted characters, or in this case exchanging one sub-string for another.
The second and third parameters do not have to be the same length. If they
are not, then the length of the overall string will change. In RPG,
%REPLACE functions like %SUBSTR on the left side of the assignment, except
it returns the result as well as updating the string.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx

-----midrange-l-bounces@xxxxxxxxxxxx wrote: -----
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
From: Mike Wills
Sent by: midrange-l-bounces@xxxxxxxxxxxx
Date: 12/06/2011 05:15PM
Subject: Re: Is hex 00000 in a string field equal to null?

Thanks, I'll try all of these options.

--

Mike Wills
http://mikewills.me


On Tue, Dec 6, 2011 at 3:37 PM, Richard Casey <casey_r@xxxxxxxxxxxxxxxx

wrote:

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.

--

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.

--
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.