RE: SQL Is there a better way than multiple REPLACEs -- MIDRANGE-L

It you want to "Translate" multiple different characters in multiple other
different charactes, you should have a look at TRANSLATE

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization ? Education ? Consulting on IBM i

IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Don
Brown via MIDRANGE-L
Sent: Friday, 12 May 2023 09:24
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Don Brown <DBrown@xxxxxxxxxx>; MIDRANGE-L
<midrange-l-bounces@xxxxxxxxxxxxxxxxxx>; Vern Hamberg
<vhamberg@xxxxxxxxxxxxxxx>
Subject: Re: SQL Is there a better way than multiple REPLACEs

Hi Vern,

As I said in my post this data is NOT coming from IBMi but as you have
correctly ascertained from mySQL.

I did not write this but I have requested changes every time we get invalid
json data which subsequently requires an additional replacement character.

I too looked up the GROUP_CONCAT(DISTINCT as it was not something I was
familiar with and it is being used to select values uniquely from a list.

The area I was looking to simplify was having to have an additional
replace() for every character we need to remove.

Thanks for your reply.

Cheers

Don

From: "Vern Hamberg via MIDRANGE-L" <midrange-l@xxxxxxxxxxxxxxxxxx>
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: "Vern Hamberg" <vhamberg@xxxxxxxxxxxxxxx>
Date: 12/05/2023 04:57 PM
Subject: Re: SQL Is there a better way than multiple REPLACEs
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

Hi Don

I was trying to play with this a bit - I have to address first, namely, that
GROUP_CONCAT is not a function on IBM i - seems, according to Google, that
it is MySQL's replacement of the standard LISTAGG aggregate function.

RSS in ACS also had difficulty with matching parentheses - so my question -
is this intended to concatenate a list of values, coming from multiple rows?

It might help to see an example of the original data you use this over.

Cheers
Vern

On 5/11/2023 9:44 PM, Don Brown via MIDRANGE-L wrote:

Here is an example.

Is there a better way ?

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(GROUP_CONCAT(DISTINCT

pick.name),CHAR(10),'\\n'),CHAR(13),''),'[','('),']',')'),'"','\''),CHAR(9),
'')

as PickupName

The output needs to be valid JSON

We have no control over the creation of the data.

The data is coming from NON DB2 and we have to load the results to DB2

Thanks

Don

--
This email has been scanned for computer viruses. Although MSD has
taken

reasonable precautions to ensure no viruses are present in this email, MSD
cannot accept responsibility for any loss or damage arising from the use of
this email or attachments..

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

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.