I am using REGEXP_REPLACE to search multiple source strings (>1000) in a column1 of table1 and replace with pattern 'xyz' using select statement. But I am getting below error as REGEXP_REPLACE has limitation of 512 bytes.
ORA-12733: regular expression too long
I was wondering if there is any work around for it.
Below is my initial query.
select REGEXP_REPLACE(table1.Column1,'SearchString1|SearchString2|SearchString1|.....SearchString1000','xyz')
from table1
My query would be very long if I use below solution.
Can it be done in loop using shell script?
I don't know whether you can do it in loop using shell script, but - why? Regular expressions still work, only if you adjust it a little bit.
I'd suggest you to store search strings into a separate table (or use a CTE, as in the following example). Then outer join it to the source table (test in my example) and - see the result.
Sample data:
SQL> with
2 test (col) as
3 (select 'Littlefoot' from dual union all
4 select 'Bigfoot' from dual union all
5 select 'Footloose' from dual union all
6 select 'New York' from dual union all
7 select 'Yorkshire' from dual union all
8 select 'None' from dual
9 ),
10 search_strings (sstring) as
11 (select 'foot' from dual union all
12 select 'york' from dual
13 )
Query:
14 select t.col,
15 regexp_replace(t.col, s.sstring, 'xyz', 1, 1, 'i') result
16 from test t left join search_strings s on regexp_instr(t.col, s.sstring, 1, 1, 0, 'i') > 0;
COL RESULT
---------- --------------------
Littlefoot Littlexyz
Bigfoot Bigxyz
Footloose xyzloose
New York New xyz
Yorkshire xyzshire
None None
6 rows selected.
SQL>