Qanat Tajrediat Qanat Tajrediat - 6 months ago 16
SQL Question

Replace All Occurrences After a Character using Oracle regexp_replace

I want to replace ALL ', with '|| .However, I only replace ', that show up after the very first word begin (case insensitive). We might have multiple BEGIN, but once I meet the word begin for the first time, I want to replace all occurrences AFTER begin.

Here is my code that replaces ALL without exception:

regexp_replace(column_name, ''',', '''||', 1, 0, 'i');


Thanks

Answer

You could simply find the position of the first word begin in your column and add to position parameter of regexp_replace

So you should use this

regexp_replace(column_name, ''',', '''||', instr(upper(column_name),'BEGIN'), 0, 'i');

Another way to find position of word begin is use REGEXP_INSTR. Not sure about performance between instr(upper()) vs regexp_instr

regexp_replace(column_name, ''',', '''||', regexp_instr(column_name, 'begin', 1, 1, 0, 'i'), 0, 'i');