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')