CODEWITHSUNDEEP
sqlregexoracle-databaseoracle-sqldeveloper
Qanat Tajrediat
Qanat Tajrediat

Reputation: 137

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

Upvotes: 1

Views: 842

Answers (1)

Pham X. Bach
Pham X. Bach

Reputation: 5442

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

Upvotes: 2

Related Questions