CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
Jenifer
Jenifer

Reputation: 347

Using Regular Expression in Snowflake

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?

Upvotes: 0

Views: 149

Answers (1)

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1608

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') 
)  ;

Upvotes: 1

Related Questions