postgres SQL update column with value from regex + more

Question

I want to update all matched url's in db.table.column. I want to add a param in the end of the url and leave everything else as it was before.

I have tried this:

UPDATE table SET column = regexp_replace(column, 'url_matcher', (substring(column, 'url_matcher') || 'end_param');

UPDATE
    "db"."emailTemplate"
set 
    "bodyText" = regexp_replace(
        "bodyText",
        '({{(@root\.)?app\.url}})[a-zA-Z0-9@:%._\+~#\-&={}\/?]{2,255}',
        (substring("bodyText" from '({{(@root\.)?app\.url}})[a-zA-Z0-9@:%._\+~#\-&={}\/?]{2,255}') || '&param={{param}}')
    ),
    "bodyHtml" = regexp_replace(
        "bodyHtml",
        '({{(@root\.)?app\.url}})[a-zA-Z0-9@:%._\+~#\-&={}\/?]{2,255}',
        (substring("bodyHtml" from '({{(@root\.)?app\.url}})[a-zA-Z0-9@:%._\+~#\-&={}\/?]{2,255}') || '&param={{param}}')
    );

but the substring does not match the whole url it only matches {{app.url}} or {{@root.app.url}} everything else of the url is not matched.

When I alone run regexp_replace("bodyHtml", [same pattern], 'replace_thing') it matches the whole url and replaces that with "replace_thing". So in one case the regex pattern works, and in the other it doesn't. All fields that didn't match a url is not updated, but when using the substring function also, as in my example. all fields that didn't match a url is updated with null.

Can I not use nested functions like this? if not - how can I achieve what I want otherwise?
Does other regex pattern rules apply to substring function than regexp_replace?

emaxple of column value: Hello user, click here {{app.url}}/home:search=true&item=33 to visit your page regexp_replace will match: {{app.url}}/home:search=true&item=33, substring will only match {{app.url}}

after update the column value should look like this:

Hello user, click here {{app.url}}/home:search=true&item=33&param={{param}} to visit your page

but it ends up looking like this:

Hello user, click here {{app.url}}&param={{param}} to visit your page

a field like this: Hello you have been registered! will look like this: null but should be untouched.

It looks you just need a '({{(@root\.)?app\.url}}[a-zA-Z0-9@:%._+~#&={}/?-]{2,255})' regex and '\1&param={{param}}' replacement. See rextester.com/MHVIX41178. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jun 13, 2018 at 8:38
Thanks, this helped me a lot already! I didn't know about \1 as reference. — Rasmus Puls
– Rasmus Puls, Commented Jun 13, 2018 at 8:53

Wiktor Stribiżew · Accepted Answer · 2018-06-13 08:57:20Z

1

Regex patterns are used to search for matching texts. Inside the replacement patterns, you may not use regex, only specific constructs, e.g. replacement backreferences like \1 to refer to capturing group 1 value. Besides, you need special callbacks to modify match values during replacement, and this is not supported here.

You may wrap the whole pattern with ( and ) (to create a capturing group) and use a regex like this:

'({{(@root\.)?app\.url}}[a-zA-Z0-9@:%._+~#&={}/?-]{2,255})'

The replacement will look like

'\1&param={{param}}'

where \1 is a replacement backreference to Group 1 value. See the online demo

select regexp_replace('Hello user, click here {{app.url}}/home:search=true&item=33 to visit your page',
                      '({{(@root\.)?app\.url}}[a-zA-Z0-9@:%._+~#&={}/?-]{2,255})',
                      '\1&param={{param}}');

answered Jun 13, 2018 at 8:57

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rasmus Puls Over a year ago

It seems to me that \1 (group 1) only matches {{app.url}} \2 (group 2) only matches @root. - in chases where that i present. what is the back reference for the full match ?

Wiktor Stribiżew Over a year ago

@RasmusPuls wrap the whole pattern with ( and ) (to create a capturing group). PostgreSQL regexp_replace does not support a replacement backreference to the whole match value. Besides, make sure the - and + are not escaped, and move - to the end of the bracket expression. Just use my expression from the answer. The \/ in your pattern is actually two chars, \ and /, it is not an escaped / that would only match / in some, say, JS regex.

Collectives™ on Stack Overflow

postgres SQL update column with value from regex + more

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related