是否可以将REPLACE与LIKE组合以替换oracle数据库列中的多个值

This is similar to the question here but instead of replacing a single value I want to replace multiple values with matching pattern.

这与此处的问题类似,但我不想替换单个值,而是使用匹配模式替换多个值。

--create table
create table my_table (column1 varchar2(10));

--load table
insert into my_table values ('Test1');
insert into my_table values ('Test2');
insert into my_table values ('Test3');
insert into my_table values ('Test4');
insert into my_table values ('Test5');
insert into my_table values ('Lesson');



--this query replaces 'Test1' with blank
select replace(column1, 'Test1', ' ') from my_table;

--now i want to replace all matching values with blank but i get an error
select replace(column1, like '%Test%', ' ') from my_table; --this throws below error.


--ORA-00936: missing expression
--00936. 00000 -  "missing expression"
--*Cause:    
--*Action:
--Error at Line: 19 Column: 25

Running Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

运行Oracle Database 11g企业版11.2.0.1.0版

2 个解决方案

#1

I would use regexp_replace.

我会用regexp_replace。

select regexp_replace(column1,'Test[[:digit:]]', ' ') from my_table;

regexp_replace

#2

In the original post, you were indicating by %Test% that you want to replace the entire string with a space if it had the string "Test" anywhere in it:

在原始帖子中,您通过%Test%指示要将整个字符串替换为空格,如果其中包含字符串“Test”:

with my_table(col1) as
( select 'Test1' from dual
  union
  select 'Test2' from dual
  union
  select 'thisisaTestofpatternmatching4' from dual
  union
  select 'thisisa Test ofpatternmatching5' from dual  
  union
  select 'Test at the start' from dual    
  union
  select 'Testat the start no following space' from dual   
  union
  select 'Ending with Test' from dual    
  union
  select 'Ending nospacebeforewithTest' from dual     
  union
  select 'Testy' from dual        
  union
  select 'Lesson' from dual    
)
select regexp_replace(col1, '^.*Test.*$', ' ') from my_table;

I suspect you really only want to replace the word Test though? Can it occur more than once in a line?

我怀疑你真的只想替换Test这个词吗?它可以在一条线上多次出现吗?

select regexp_replace(col1, 'Test', ' ') from my_table;

The word test followed by a digit?

单词测试后跟一个数字?

select regexp_replace(col1, 'Test\d', ' ') from my_table;

Tip: Make sure your test cases are set up to include all kinds of combinations of your test data, even where they may be unexpected. When testing your regular expressions, sometimes you may get unexpected results so make sure all possible conditions are tested.

提示:确保您的测试用例设置为包含测试数据的各种组合,即使它们可能是意外的。在测试正则表达式时,有时可能会出现意外结果,因此请确保测试所有可能的条件。

#1