OTARIKI OTARIKI - 7 months ago 9
SQL Question

regexp_replace replace every piece in text

I have string like this:

aa/ss/[img]aa/ss/dd[/img][img]aa/ss/dd[/img]aa/ss


I need replace this piece
/ss/
with this:
/WW/
but only when they are between
[img]
tags. result must be:

aa/ss/[img]aa/WW/dd[/img][img]aa/WW/dd[/img]aa/ss


I am trying this:

select regexp_replace('aa/ss/[img]aa/ss/dd[/img][img]aa/ss/dd[/img]aa/ss', '(\[img\].*/)ss(/.*\[\/img\])', '\1WW\2', 'g')


But this replaces just one piece, not both. I use
'g'
flag buth same result.

How to do this correctly ?

Answer

The problem is the "greediness" of the operators. You have repeated expressions so either ss could match the pattern once or twice -- depending on whether the middle '[/img][img] is counted as the match to the .* or to the fixed pattern.

Unfortunately, it is easier for me to diagnose the problem than fix it -- I find greediness in regular expressions to simply be confusing. But, here is a fix for your particular string. It just takes the '[' character into account:

select regexp_replace('aa/ss/[img]aa/ss/dd[/img][img]aa/ss/dd[/img]aa/ss',
                      '(\[img\][^[]*/)ss(/[^[]*\[\/img\])',
                      '\1WW\2',
                      'g')