× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I like that, I'll try 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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.