Need help in using REGEXP function

Question

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?

By mistake deleted the answer which someone replied. Sorry.

Jenifer
– Jenifer

2022-02-25 10:39:54 +00:00
Commented Feb 25, 2022 at 10:39 — Jenifer
– Jenifer, Commented Feb 25, 2022 at 10:39

Simeon Pilgrim · Accepted Answer · 2022-02-25 11:08:34Z

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

Jakub · Accepted Answer · 2022-02-25 10:49:15Z

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

edited Feb 25, 2022 at 10:49

answered Feb 25, 2022 at 10:38

Jakub

1001 silver badge6 bronze badges

2 Comments

Jenifer Over a year ago

I have tried the logic you said but still showing the same result.

Jakub Over a year ago

@Jenifer Yeah sorry I misread your problem. My solution won't work in your situation. Simeon solution is the right one. My bad

Collectives™ on Stack Overflow

Need help in using REGEXP function

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related