Trouble defining RegEx pattern for a substring with repeated instances

Question

I'm relatively new to RegEx and have been looking through various tutorials but struggling to understand how to put together the pattern I need.

Currently I have a string of SQL that includes repeated instances of this pattern:

,coalesce(column1,null) AS table_name.column1

table_name could be any name given that there are multiple tables involved in the overall SQL code. What I'm trying to do is eliminate table_name from each occurrence of this pattern. I'm thinking as long as I can identify the positions of AS and the . then the characters in between can be replaced with "".

Would I be incorrect in that method?

Does this work for you? https://regex101.com/r/fYwQ7d/1 – CAustin Feb 21 '20 at 01:39 — CAustin, Feb 21 '20 at 01:39

score 0 · Accepted Answer · answered Feb 21 '20 at 01:45

I would just do a global replacement on:

\bAS\s*\S+?\.(\S+)

and use the first capture group as the replacement.

Sample script:

inp = ",coalesce(column1,null) AS table_name.column1"
out = re.sub(r'\bAS\s*\S+?\.(\S+)', '\\1', inp)
print(out)

This prints:

,coalesce(column1,null) column1

Note that this regex search is fairly robust, as the AS keyword in SQL is mainly used to mark aliases.

Trouble defining RegEx pattern for a substring with repeated instances

1 Answers1