samedi 9 mai 2015

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

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

Aucun commentaire:

Enregistrer un commentaire