I have a string that will be used in an oracle query that I need to parse and change the alias of the columns in the string to a different value and I need to use a regular expression to do so.
Here is an example of the sort of string I will be dealing with:
ph.activity = ''ph.activity.1'' AND ((ph.activity = ''ph.act.1'' AND ph.activity = ''test.ph.act.1'' AND ph.activity = ''ph.test.23''))
I need to change all the ph. in the string where the ph. appears at the start of the string, where it appears after a ( character and where it appears after a whitespace and not any others.
So far for my regular expression I have this:
This doesn’t work for me as it also captures the whitespace character preceding the ph. as well as the ( character preceding its ph.
How can I modify this expression to capture the ph. without also capturing the ( and whitespace character?
You could change the pattern to have a single capturing group that allows start-anchor, opening parenthesis or space:
and in the replace string, prefix your value with
\1 so it includes whatever that matched.
select regexp_replace( 'ph.activity = ''ph.activity.1'' AND ((ph.activity = ''ph.act.1'' AND ph.activity = ''test.ph.act.1'' AND ph.activity = ''ph.test.23''))', '(^|\(| )ph\.', '\1TEST.') as result from dual
|TEST.activity = ‘ph.activity.1’ AND ((TEST.activity = ‘ph.act.1’ AND TEST.activity = ‘test.ph.act.1’ AND TEST.activity = ‘ph.test.23’))|