Regex for Meta-SQL Conversion

I've come up with the following regular expressions to add or remove %Table() meta-sql construct. Applications might involve using it to replace standard table names with the %Table() Meta-SQL directive before putting them into the Application Designer SQL Editor or using it to remove the %Table() Meta-SQL directive if you don't have the option to resolve meta-sql. It's also just a good example of regex to match the start and end of a qualifier like brackets and replace them with something else.

Here are the four scenarios that you might have:

Regular PeopleSoft Tables

This is for anything that uses PS_ in your database as a prefix:

Scenario 1: Find something like PS_INSTALLATION and replace with %Table(INSTALLATION)

Find Regex: PS_([^\s]*)
Replace Regex: %Table($1)

View Example in RegExr.

Scenario 2: Find something like %Table(INSTALLATION) and replace with PS_INSTALLATION

Find Regex: %Table\(((?!PS)[^\s]*)\)
Replace Regex: PS_$1

View Example in RegExr.

PeopleTools Tables

This is specifically for PeopleTools tables that always start with PS and do not use the PS_ prefix for the database table name.

Scenario 3: Find something like PSOPRDEFN and change to %Table(PSOPRDEFN)

Find Regex: PS((?!_)[^\s]*)
Replace Regex: %Table(PS$1)

View Example in RegExr.

Scenario 4: Find something like %Table(PSOPRDEFN) and change to PSOPRDEFN

Find Regex: %Table\(((PS)[^\s]*)\)
Replace Regex: $1

View Example in RegExr.

Try it out in your text editor with regular expressions enabled. You may need to put qualifiers around the find regex, e.g. /%Table\(((PS)[^\s]*)\)/g depending on the regex engine you are using.