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

- Wil

---

你使用的Oracle版本是什么？ - wolφi

@wolφi，抱歉我没有提供那个信息。我们使用的是11g版本。 - Wil

Answer 1

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

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

- vnskv

---

不幸的是，如果逗号之前的“SL”后面还有其他字母，则此方法将无法奏效。就像“OSL”示例中可以有字母一样，在逗号之前也可以有字母，例如“SLR”。我已经将这个例子添加到问题中。 - Wil

如果没有更好的替代方案，那么我认为最好的方法是使用替换进行第二次处理。regexp_replace(regexp_replace(col1,'(^|,)(SL)(\W|$)','\1\3',1,0,'imn') ,'(^|,)(SL)(\W|$)','\1\3',1,0,'imn') - vnskv

很有趣你提到了那个，那正是我在尝试不同情况时偶然发现的。如果你想将其作为答案添加，我会接受它。 - Wil

单个的 regexp_replace 是可以实现的（需要一些前置和后置处理）- 可以查看替代方案的答案。 - Marmite Bomber

Answer 2

如果您可以选择一个字符串中不存在的特殊字符 - 我使用分号（;），您可以按照以下步骤操作：

1）将每个逗号加倍为逗号+分号

2）仅将分号作为可能的分隔符添加到模式中，执行您的正则表达式：

'(^|,|;)(SL)($|,|;) 

3) 移除所有分号

完整查询

SELECT 
    replace(regexp_replace(replace(col1,',',',;'),'(^|,|;)(SL)($|,|;)','\1\3',1,0,'imn'),';') as col2
FROM tab;

产生预期结果

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

- Marmite Bomber