How to replace comma in the beginning and end of the result while using LISTAGG? I tried LTRIM but not getting the correct result.

SELECT s_id
      ,REGEXP_REPLACE(rewrd_card_nbr, '\null|id not found|no value|no-value|blank|null|''|', '') 
       AS RESULT
      ,MIN(TRY_TO_NUMBER(other.item_pg_nbr)) AS item_pg_nbr
FROM table_1 S
JOIN table_2 P
ON P.click_stream_integration_id = S.click_stream_integration_id
WHERE s_id IN    ('1217423213611638899263'
               ,'2988822682711638898987'
               ,'8131301001481638906163'
               ,'8030711031105116372833'
               ,'7806180295006814765443'
               ,'6215684539960126690425'
               ,'6502689315628777977858'
               ,'6959274311947723444672'
               ,'1235659053876083630210'
               ,'5958673331149776209626'
               ,'1587460374618074937485'
)
GROUP BY s_id,rewrd_card_nbr
ORDER BY TRY_TO_NUMBER(item_pg_nbr);

After adding LISTAGG in the Outer Query, I'm getting the result -

1. only comma for few rows
2. comma at the beginning and end
3. Blank value
4. ,,

I want the commas to be removed and the blank value to be replaced as 'NO VALUE'

Something is missing in my code. Please share your thoughts. Thanks!

CodePudding user response：

Your listagg is listing blank strings and delimiting them. To suppress that, you can change blank strings to NULL, which will not appear in the listagg at all so they will not require a delimiter.

select listagg(COLUMN1, ',') from values(''),(''); -- listagg two blank strings results in a single comma
select listagg(COLUMN1, ',') from values(''),(''),('12345'); -- Results in two commas followed by a number
select listagg(COLUMN1, ',') from values(''),(''),('12345'),(''); -- Results in commas at the start & end

select listagg(iff(COLUMN1='', null, COLUMN1), ',') from values(''),(''); -- listagg two blank strings results in a single comma
select listagg(iff(COLUMN1='', null, COLUMN1), ',') from values(''),(''),('12345'); -- Results in two commas followed by a number
select listagg(iff(COLUMN1='', null, COLUMN1), ',') from values(''),(''),('12345'),(''); -- Results in commas at the start & end

You can also do it in your REGEXP_REPLACE and won't have to do it in your listagg:

select REGEXP_REPLACE('id not found', '\null|id not found|no value|no-value|blank|null|''|', null) 

AS RESULT