雪花云数据平台-在使用REGEXP功能时需要帮助

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;

当我运行上面的查询时，我得到了类似的结果

ID                 COL_001
5871162            ,monthend_offer
35915895           dec_cashback,dec_offer

如果我用空字符串替换逗号，结果将如下所述，这不是的例外结果

5871162            monthend_offer
35915895           dec_cashbackdec_offer

我只想替换第一条记录中的逗号"，"。记录应显示如下：

5871162            monthend_offer
35915895           dec_cashback,dec_offer

我只想替换前导逗号

有关于如何实施的指导吗？

Answer 1

要修剪字符串，可以：

SELECT 
column1, 
ltrim(column1, ','),
regexp_replace(column1, '^,')
FROM VALUES
(',monthend_offer'),
('dec_cashback,dec_offer');

但出现问题的原因是col_1中有一个空字符串，该字符串正在您的listagg中汇总。

CASE语句(这个：(

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

可以写成：

IFNULL(NULLIF(lower(LISTAGG(distinct column2,',') WITHIN GROUP ( ORDER BY column2)),''),'null') as Col_001

如图所示：

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	，月底报价	，月末报价
2	dec_cashback，dec_offer

如果我理解正确，您可以使用另一个正则表达式。

select REGEXP_REPLACE(REGEXP_REPLACE(col_1,'http.*$|null', '')),',','',1,1) as COL1;

1表示regex从哪里开始，另1表示应该替换多少。

希望能帮助