我正在寻找一种方法,在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 正则表达式的解决方案。
更新
- 版本:我们使用的是 Oracle 11g 版本。
- 新增示例用例
PI,SL,SLR,PK
- 新增示例用例
PK,SL
、SL,SL
、SL
和PK