我正在寻找一种方法来替换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
2条答案
按热度按时间v64noz0r1#
因为oracle的regex在匹配后将匹配位置向前移动,所以不幸的是,您需要执行两次regexp
nzrxty8p2#
如果你能选择一个不出现在字符串中的特殊字符-我用的是分号(
;
)您可以按照以下步骤操作1) 将每个逗号加倍为逗号+分号
2) 只执行regexp将分号作为可能的分隔符添加到模式中:
3) 删除所有分号
完整的查询
产生预期结果