Workaround for REGEXP_REPLACE in Oracle SQL | Regular Expression too long-CodePudding

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?

https://stackoverflow.com/questions/21921658/oracle-regular-expression-regexp-like-too-long-error-ora-12733

CodePudding user response：

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>