Regexp_replace在雪花中

发布于 2025-01-23 01:59:05 字数 335 浏览 0 评论 0原文

我在雪花表中有旧的_column，并希望将值转换为新列，如下所述。

我尝试了Regexp_replace，无法编写精确的逻辑。

old_column	new_column
1,2,4	ACT，星期六，TOEFL
6,3	lnat，雅思
5,1	ACT，BMAT

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

執念 2025-01-30 01:59:05

您的new_columns不会显示随机订单。假设其订单，一个查询的一个版本可以如下（可以根据数据值进一步添加情况） -

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      |
+-----------+---------------+

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      |
+-----------+---------------+

回复收藏 0 原文

~没有更多了~