Regular expression to capture alias in a string to be used in an oracle query without capturing characters that are the same as the alias

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:

(^ph\.)|( ph.)|(\(ph.)

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?

>Solution :

You could change the pattern to have a single capturing group that allows start-anchor, opening parenthesis or space:

'(^|\(| )ph\.'

and in the replace string, prefix your value with \1 so it includes whatever that matched.

For example:

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

fiddle