Re: SQL Is there a better way than multiple REPLACEs

[Don Brown <DBrown@xxxxxxxxxx>]

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