Is it possible to combine REPLACE with LIKE to replace multiple values in oracle database column

Question

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

Use regexp_replace(column1, '^.*Test.*$',' ')

Egor Skriptunoff
– Egor Skriptunoff

2015-05-08 14:20:09 +00:00
Commented May 8, 2015 at 14:20 — Egor Skriptunoff
– Egor Skriptunoff, Commented May 8, 2015 at 14:20

Nick · Accepted Answer · 2015-05-08 14:21:53Z

3

I would use regexp_replace.

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

regexp_replace

answered May 8, 2015 at 14:21

Nick

2,51419 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary_W · Accepted Answer · 2015-05-08 14:40:09Z

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:

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?

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.

Collectives™ on Stack Overflow

Is it possible to combine REPLACE with LIKE to replace multiple values in oracle database column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related