CODEWITHSUNDEEP
postgresql
Shashank Shandilya
Shashank Shandilya

Reputation: 39

How to replace second occurrence of word in postgre sql using SQL?

Eg.

select regex_replace('the growth is growth for', '?','good','ig');

it should return the growth is good for

Upvotes: 1

Views: 826

Answers (1)

Thom Brown
Thom Brown

Reputation: 2039

You can match the word you are looking for, followed by everything up to the 2nd word, followed by the word you are looking for again, then replace it with the first two matches, followed by the word you want to replace it with.

For example:

SELECT regexp_replace(
  'the growth is growth for lots of growth',
  '(growth)(.*?)(\1)',
  '\1\2good', 'ig'
);

            regexp_replace             
---------------------------------------
 the growth is good for lots of growth
(1 row)

Here, the (.*?) is matching everything after the first "growth" text, but to stop matching once it hits the next "growth" text, denoted by \1, meaning whatever was matched in the first group enclosed in parentheses. The ? character makes it non-greedy, matching the minimum needed. If it weren't there, it would match as much as it can, which would keep going if there were more than 2 instances to only replace the last.

Upvotes: 3

Related Questions