Postgresql regexp_replace: how to replace a captured group with an evaluation expression (adding an integer value to capture the group)

Question

I need to convert some lines in this format:

B12F34

like that:

Building 12 - Floor 34

but I need to add a value, say 10, to the second capture group, so the new line will be as follows:

Building 12 - Floor 44

I can use this postgres clause to get almost everything, but I don't know how to add a value to the second capture group.

SELECT regexp_replace('B12F34', 'B(\d+)F(\d+)', 'Building \1 - Floor \2', 'g'); 

I was looking for a way to add a value to \ 2, but all I found is that I can use the 'E'-modifier, and then \ 1 and \ 2 should be \\ 1 and \\ 2

 SELECT regexp_replace('B12F34', 'B(\d+)F(\d+)', E'Building \\1 - Floor \\2', 'g') 

I need some suggestion like this:

 SELECT regexp_replace('B12F34', 'B(\d+)F(\d+)', E'Building \\1 - Floor \\2+10', 'g') 

to get ........ Floor 44 instead of ........ Floor 34 + 10

Thanks in advance!

Answer 1

You cannot do this only in regex because regexp does not support math on captured groups, even if they are all numeric characters. So you should get a group that represents the gender number, do the math, and combine it into:

 SELECT regexp_replace('B12F34', 'B(\d+)F(\d+)', 'Building \1 - Floor ') || ((regexp_matches('B12F34', '[0-9]+$'))[1]::int + 10)::text; 

Not very efficient due to two regexp calls. Another option is to simply get two numbers in the subquery and collect the string in the main query:

 SELECT format('Building %L - Floor %L', m.num[1], (m.num[2])::int + 10) FROM ( SELECT regexp_matches('B12F34', '[0-9]+', 'g') AS num) m;