[SOLVED] Postgres - Replace full String including occurances using regexp

Postgres - Replace full String including occurances using regexp_replace

I am using Postgres regexp_replace method to :

Replace *.dataMain AS *__dataMain, full Row as Empty, where * can be any alias for ex. m, n, m2, n1, etc any alphanumeric.

I have below sample data :

n.dataPrev AS n__dataPrev,
n.dataMain AS n__dataMain,
m.dataPrev2 AS m__dataPrev,
m.dataMain AS m__dataMain,
m.dataNext2 AS m_dataNext2

Expected Output :

n.dataPrev AS n__dataPrev,
m.dataPrev2 AS m__dataPrev,
m.dataNext2 AS m_dataNext2

Below is my test regexp_replace method usage to try :

select regexp_replace(
'n.dataPrev AS n__dataPrev, n.dataMain AS n__dataMain, m.dataPrev2 AS m__dataPrev, m.dataMain AS m__dataMain, m.dataNext2 AS m_dataNext2',
'[^:\s]+dataMain(\S+)', '');

Getting below as output :

n.dataPrev AS n__dataPrev,
n.dataMain AS 
m.dataPrev2 AS m__dataPrev,
m.dataMain AS m__dataMain,
m.dataNext2 AS m_dataNext2

Solution

This seems to work for me:

select regexp_replace(
'n__dataPrev, n.dataMain AS n__dataMain, n.dataNext
 m__dataPrev2, m.dataMain AS m__dataMain, m.dataNext2',
', [^,]* AS [^,]*,', ',','g');

The regex I am using:

, [^,]* AS [^,]*,

It is searching for "AS" and matches everything between the 2 commas. I changed the flag to 'g' as well which replaces all occurrences instead of only the first. Let me know if this works for you.

Update

If you only want to match dataMain try this Regex:

,[^,]*dataMain[^,]*,