oracle regex替换多个出现的由逗号包围的字符串

kmb7vmvb  于 2021-08-13  发布在  Java
关注(0)|答案(2)|浏览(524)

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

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

我已经在其他具有单词边界的regex实现中成功地实现了我的目标 \b 构造可用,但尚未找到针对oracle正则表达式的解决方案。
更新
版本:我们使用的是oracle版本11g。
附加示例案例 PI,SL,SLR,PK 其他示例案例 PK,SL , SL,SL , SL , PK

v64noz0r

v64noz0r1#

因为oracle的regex在匹配后将匹配位置向前移动,所以不幸的是,您需要执行两次regexp

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

nzrxty8p2#

如果你能选择一个不出现在字符串中的特殊字符-我用的是分号( ; )您可以按照以下步骤操作
1) 将每个逗号加倍为逗号+分号
2) 只执行regexp将分号作为可能的分隔符添加到模式中:

'(^|,|;)(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

相关问题