oracle 字符串自身按分隔符去重

～雄

已于 2024-03-26 17:27:13 修改

阅读量1.5k

点赞数

分类专栏： oracle 文章标签：数据库 sql oracle

于 2022-12-02 16:11:57 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_44543121/article/details/128149931

版权

oracle 专栏收录该内容

5 篇文章

订阅专栏

CREATE OR REPLACE FUNCTION BI.SF_SPLIT_ACCOUNT_ID_LIST (--字符串按分隔符自身去重：需要去重的字符串，分隔符
account_id_list IN VARCHAR2,
Separator  IN VARCHAR2
)RETURN VARCHAR2 AS
v_account_id_list VARCHAR2(5000);

BEGIN

WITH ACCTS AS (
SELECT DISTINCT str from (
SELECT REGEXP_SUBSTR(replace(account_id_list,Separator||' ',Separator), '[^'||Separator||']+', 1, LEVEL, 'i') AS STR
FROM DUAL
CONNECT BY LEVEL <=
LENGTH(replace(account_id_list,Separator||' ',Separator))
- LENGTH(REGEXP_REPLACE(replace(account_id_list,Separator||' ',Separator), Separator, ''))+1))
SELECT LISTAGG(STR,Separator) WITHIN GROUP(ORDER BY STR desc)  INTO v_account_id_list  FROM (select * from ACCTS order by STR desc);

RETURN v_account_id_list;

END SF_SPLIT_ACCOUNT_ID_LIST;