如何更新/更改Oracle数据库中字符串的一部分

Question

我必须更新Oracle数据库删除字符串的一部分，问题是，这部分可以在多个地方在这个字符串。例子:

我必须从

中删除部分并更新数据库，其中clolumn_name like ('%，aaa%')'bbb,aaa,ccc,ddd'或'bbb,ccc,aaa,ddd'或'bbb,ccc,eee,fff,aaa,ddd'

请帮帮我:)

Answer 1

使用简单的字符串函数(比正则表达式快得多)替换完整的术语是:

SELECT TRIM(
BOTH ',' FROM
REPLACE(','||value||',', ',aaa,', ',')
) AS replaced_value
FROM   table_name

对于样本数据:

CREATE TABLE table_name ( value ) AS
SELECT 'aaa' FROM DUAL UNION ALL
SELECT 'aaa,bbb' FROM DUAL UNION ALL
SELECT 'ccc,aaa' FROM DUAL UNION ALL
SELECT 'ddd,aaa,eee' FROM DUAL UNION ALL
SELECT 'fff,aaa,ggg,aaa,hhh' FROM DUAL UNION ALL
SELECT 'aaa,aaa,aaa' FROM DUAL;

输出:

REPLACED_VALUE空ddd, eeefff ggg,终极战士3

您可以在这里使用正则表达式替换方法:

SELECT val, TRIM(BOTH ',' FROM REGEXP_REPLACE(val, 'aaa,|,aaa,|,aaa', ',')) AS val_out
FROM yourTable;

演示如果需要更新，则使用:

UPDATE yourTable
SET val = TRIM(BOTH ',' FROM REGEXP_REPLACE(val, 'aaa,|,aaa,|,aaa', ','))
WHERE ',' || val || ',' LIKE '%,aaa,%';