0 
SELECT ID, CASE WHEN listagg( DISTINCT col_1,',') WITHIN GROUP(ORDER BY col_1)= '' THEN 'null' ELSE (lower(LISTAGG(distinct col_1,',') WITHIN GROUP ( ORDER BY col_1))) END AS Col_001 FROM (SELECT distinct B.ID, date, timestamp, TRY_CAST(pno as INTEGER) as pno, REGEXP_REPLACE(col_1,'\http.*$|null', '') as col_1 FROM table1 B LEFT JOIN table2 D ON D.ID=B.ID WHERE B.ID IN('5871162','35915895') and date='2021-11-02' ORDER BY pno) GROUP BY ID;

When I run the above query, I'm getting results like

ID COL_001 5871162 ,monthend_offer 35915895 dec_cashback,dec_offer

If I replace comma with empty string, the result will be like mentioned below and that is not the excepted result

 5871162 monthend_offer 35915895 dec_cashbackdec_offer

I want to replace only the comma ',' from the first record. The record should display like below:

5871162 monthend_offer 35915895 dec_cashback,dec_offer

I want to replace only the LEADING comma

Any guidance on how to implement this?

Improve this question
1
  • By mistake deleted the answer which someone replied. Sorry. Commented Feb 25, 2022 at 10:39

2 Answers 2

Reset to default
2

To trim a string you can:

SELECT column1, ltrim(column1, ','), regexp_replace(column1, '^,') FROM VALUES (',monthend_offer'), ('dec_cashback,dec_offer');

But the reason you have the problem is you have an empty string in col_1 which is getting rolled up in your listagg.

The CASE statement (this one:)

 CASE WHEN listagg(DISTINCT col_1,',') WITHIN GROUP(ORDER BY col_1)= '' THEN 'null' ELSE lower(LISTAGG(distinct col_1,',') WITHIN GROUP ( ORDER BY col_1)) END AS Col_001

can be written as:

IFNULL(NULLIF(lower(LISTAGG(distinct column2,',') WITHIN GROUP ( ORDER BY column2)),''),'null') as Col_001

As seen in:

SELECT column1, IFNULL(NULLIF(lower(LISTAGG(distinct column2,',') WITHIN GROUP ( ORDER BY column2)),''),'null') as smaler, CASE WHEN listagg(DISTINCT column2,',') WITHIN GROUP(ORDER BY column2)= '' THEN 'null' ELSE lower(LISTAGG(distinct column2,',') WITHIN GROUP ( ORDER BY column2)) END AS Col_001 from values (1,''), (2,'dec_cashback'), (1,'monthend_offer'), (2,'dec_offer') GROUP BY 1 ORDER BY 1;
COLUMN1 SMALER COL_001
1 ,monthend_offer ,monthend_offer
2 dec_cashback,dec_offer dec_cashback,dec_offer
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

if I understand correctly you can just use another regex.

select REGEXP_REPLACE(REGEXP_REPLACE(col_1,'\http.*$|null', '')),',','',1,1) as COL1;

1 for where regex starts and another 1 how many should he replace.

Hope that helps

Improve this answer

2 Comments

I have tried the logic you said but still showing the same result.
@Jenifer Yeah sorry I misread your problem. My solution won't work in your situation. Simeon solution is the right one. My bad

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.