sql - Oracle 正则表达式替换多次出现的用逗号包围的字符串

Question

我正在寻找一种方法来替换(删除/替换为'')Oracle SQL 数据库中列中以逗号分隔的值列表中的字符串。例如，假设我有以下数据:

select ('SL,PK') as col1 from dual
union all
select ('PK,SL') as col1 from dual
union all 
select ('SL,SL') as col1 from dual
union all 
select ('SL') as col1 from dual
union all 
select ('PK') as col1 from dual
union all 
select ('PI,SL,PK') as col1 from dual
union all 
select ('PI,SL,SL,PK') as col1 from dual
union all 
select ('PI,SL,SL,SL,PK') as col1 from dual
union all 
select ('PI,SL,SL,SL,SL,PK') as col1 from dual
union all 
select ('PI,OSL,SL,PK') as col1 from dual
union all 
select ('PI,SL,SLR,PK') as col1 from dual

COL1
-----
SL,PK
PK,SL
SL,SL
SL
PK
PI,SL,PK
PI,SL,SL,PK
PI,SL,SL,SL,PK
PI,SL,SL,SL,SL,PK
PI,OSL,SL,PK
PI,SL,SLR,PK

我希望用空字符串 '' 严格替换所有出现的子字符串“SL”(即不包括“OSL”)。 理想的结果应该是这样的:

COL2
-----
,PK
PK,
,
(null)
PK
PI,,PK
PI,,,PK
PI,,,,PK
PI,,,,,PK
PI,OSL,,PK
PI,,SLR,PK

我曾尝试使用 regexp_replace 函数，但它只会消除所有其他事件，即

SELECT 
    col1,
    regexp_replace(col1,'(^|,)(SL)($|,)','\1' || '' || '\3',1,0,'imn') as col2
FROM (
    SELECT ('SL,PK') as col1 FROM dual
        UNION ALL 
    SELECT ('PK,SL') as col1 FROM dual
        UNION ALL 
    SELECT ('SL,SL') as col1 FROM dual
        UNION ALL
    SELECT ('SL') as col1 FROM dual
        UNION ALL
    SELECT ('PK') as col1 FROM dual
        UNION ALL
    SELECT ('PI,SL,PK') as col1 FROM dual
        UNION ALL 
    SELECT ('PI,SL,SL,PK') as col1 FROM dual
        UNION ALL 
    SELECT ('PI,SL,SL,SL,PK') as col1 FROM dual
        UNION ALL 
    SELECT ('PI,SL,SL,SL,SL,PK') as col1 FROM dual
        UNION ALL 
    SELECT ('PI,OSL,SL,PK') as col1 FROM dual
        UNION ALL 
    SELECT ('PI,SL,SLR,PK') as col1 FROM dual
)

COL1                COL2
-----               -----
SL,PK               ,PK
PK,SL               PK,
SL,SL               ,SL
SL                  (null)
PK                  PK
PI,SL,PK            PI,,PK
PI,SL,SL,PK         PI,,SL,PK
PI,SL,SL,SL,PK      PI,,SL,,PK
PI,SL,SL,SL,SL,PK   PI,,SL,,SL,PK
PI,OSL,SL,PK        PI,OSL,,PK
PI,SL,SLR,PK        PI,,SLR,PK

我已经在其他具有单词边界 \b 构造的正则表达式实现中成功实现了我的目标，但还没有找到适用于 Oracle 正则表达式的解决方案。

更新

1. 版本:我们使用的是 Oracle 版本 11g。
2. 附加示例案例PI,SL,SLR,PK
3. 其他示例案例 PK,SL, SL,SL, SL, PK

Answer 1

因为 Oracle 的正则表达式在匹配后将匹配位置向前移动，不幸的是你需要做两次正则表达式

regexp_replace(regexp_replace(col1,'(^|,)(SL)(\W|$)','\1\3',1,0,'imn') ,'(^|,)(SL)(\W|$)','\1\3',1,0,'imn')