Replace All Occurrences After a Character using Oracle regexp_replace

Question

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

Dear Qanat Tajrediat, you should considering update your work according to the editted answer.. — Pham X. Bach
– Pham X. Bach, Commented May 26, 2016 at 2:44

Pham X. Bach · Accepted Answer · 2016-05-26 02:40:01Z

2

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, ''',', '''||', regexp_instr(column_name, '\sbegin\s', 1, 1, 0, 'i'), 0, 'i');

Editted: According to suggestion from Gary_W, you should consider using keyword '\sbegin\s' (updated in above code) to avoid finding wrong word when begin is part of another word like function get_beginning_status.

P/S: Using \s instead of non-breaking-space in case string text in column_name have line break

edited May 26, 2016 at 2:40

answered May 25, 2016 at 16:43

Pham X. Bach

5,4624 gold badges32 silver badges46 bronze badges

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

1 Comment

Gary_W Over a year ago

You might want to add a space either side of BEGIN in case the string 'BEGIN' is part of another word.

Collectives™ on Stack Overflow

Replace All Occurrences After a Character using Oracle regexp_replace

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related