REGEXP_REPLACE in snowflake

Question

I have the old_column in the snowflake table and would like to convert the values to the new column as mentioned below.

I tried with REGEXP_REPLACE and couldn’t able to write exact logic.

old_column	new_column
1,2,4	ACT,SAT,TOEFL
6,3	LNAT,IELTS
5,1	ACT,BMAT

Answer 1

Your new_columns does not shows a random order. Assuming its ordered, one version of query can be as follows (CASE can be added as needed further based on data values) -

with data_cte as
(select * from values
('1,2,4'),
('6,3'),
('5,1')
)
select distinct
listagg (value,',')
within group (order by index)
over (partition by seq) old_value,
listagg (col,',')
within group (order by value::number)
over (partition by seq) new_value
from (
select t.*,
case
when value=1 then 'ACT'
when value=2 then 'SAT'
when value=3 then 'IELTS'
when value=4 then 'TOEFL'
when value=5 then 'BMAT'
ELSE 'LNAT'
end as col
from data_cte d , table(split_to_table(d.column1,',')) t
);
+-----------+---------------+
| OLD_VALUE | NEW_VALUE     |
|-----------+---------------|
| 1,2,4     | ACT,SAT,TOEFL |
| 6,3       | IELTS,LNAT    |
| 5,1       | ACT,BMAT      |
+-----------+---------------+