Using Regular Expression in Snowflake

Question

SELECT A.ID,LISTAGG(distinct PURCHASE_ID|| ':'||PURCHASE_AMT,',')WITHIN GROUP
        (ORDER BY PURCHASE_ID|| ':'||PURCHASE_AMT) as PURCHASE_INFO_TXT
from table_1 A
JOIN table_2 B
ON B.ORD_ID=A.ORD_ID
WHERE 
B.DATE = '2022-03-03'
AND PURCHASE_CD NOT IN ('0','1','2','2')
AND A.ID IN(90818774,90818789,90818999)
GROUP BY A.ID

When I run the above query, I'm getting the below results.

ID           PURCHASE_INFO_TXT
90818789     2028805453:$19.00 off when you buy 1 
90818774     2028805443:Save $6.00 when you buy 1
90818999     2028805403:$3.94 Each

The results should look like as mentioned below:

ID           PURCHASE_INFO_TXT

90818789     2028805453:$19.00
90818774     2028805443:$6.00
90818999     2028805403:$3.94

How to achieve this?

Himanshu Kandpal · Accepted Answer · 2022-03-15 17:59:33Z

1

this may work for you using REGEXP_REPLACE.

select column1, fld1,REGEXP_REPLACE(fld1, '[^0-9_$:]') from (
  select column1,regexp_replace(column1,'when you buy 1','') fld1  from values 
  ('2028805453:$19.00 off when you buy 1 ') ,
  ('2028805443:Save $6.00 when you buy 1'), 
  ('2028805403:$3.94 Each') 
)  ;

answered Mar 15, 2022 at 17:59

Himanshu Kandpal

1,67611 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jenifer Over a year ago

The decimal values are not coming correctly. Could you please guide on that? Thanks in advance!

Himanshu Kandpal Over a year ago

@jenifer , this should work select column1, fld1,REGEXP_REPLACE(fld1, '[^0-9._$:]') from ( select column1,regexp_replace(column1,'when you buy 1','') fld1 from values ('2028805453:$19.00 off when you buy 1 ') , ('2028805443:Save $6.00 when you buy 1'), ('2028805403:$3.94 Each') ) ;

Collectives™ on Stack Overflow

Using Regular Expression in Snowflake

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related