Oracle regex 替换多次出现的由逗号包围的字符串 [英] Oracle regex replace multiple occurrences of a string surrounded by commas

问题描述

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

I am looking for a way to replace (remove / replace with '') a character string in a comma-separated list of values in a column in an Oracle SQL database. For example, suppose I have the following data:

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').理想的结果应该是这样的:

I am looking to replace all occurrences of the substring 'SL', strictly (i.e. not including 'OSL'), with an empty string, ''. The ideal result would look like this:

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

我曾尝试使用 regexp_replace 函数，但它只会消除所有其他出现的情况，即

I have tried to use the regexp_replace function, but it only eliminates every other occurence, i.e.

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 正则表达式的解决方案.

I have achieved my goal successfully in other regex implementations that have the word boundary \b construct available, but have not found a solution for Oracle's regex.

更新

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

1. Version: We are on Oracle version 11g.
2. Addition example case PI,SL,SLR,PK
3. Additional example cases PK,SL, SL,SL, SL, PK

推荐答案

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

because Oracle's regex moves the matching position forward after a match you need to do regexp double times unfortunately

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

这篇关于Oracle regex 替换多次出现的由逗号包围的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！