1

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
LuFFy
  • 8,799
  • 10
  • 41
  • 59

1 Answers1

1

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[^,]*,
Silvan
  • 395
  • 2
  • 14
  • Thanks for efforts, I updated the sample data, So the column is containing full sql inside, where I want to remove certain column definition itself in selection. Hence it will replace all the column definitions instead on the specific column, Can you update answer for single column ? for ex only for `dataMain` ? – LuFFy Jul 26 '23 at 19:32
  • 1
    @LuFFy added another regex. Does this one match your use case? – Silvan Jul 27 '23 at 06:52
  • Thank you @Silvan – LuFFy Jul 27 '23 at 10:24