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