Postgres: edit values with regex?

Question

I'd like to run a query in postgres that finds all rows in a table with a filepath of cor/* and sets them to con/*.

In pseudocode:

UPDATE photo set filepath="con/*" where filepath="cor/*";

Please could anyone help me out with the correct postgres syntax? Is this possible in postgres?

Many thanks!

user330315 · Accepted Answer · 2011-05-16 13:00:45Z

1

Regular expressions aren't really needed:

UPDATE photo
    SET filepath = 'con' || substring(filepath, 4)
WHERE filepath LIKE 'cor/%'

answered May 16, 2011 at 13:00

user330315

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

1 Comment

Clever way to do it :) Thank you very much.

Denis de Bernardy · Accepted Answer · 2011-05-16 12:58:35Z

1

There's a regexp_replace() function:

update photo
set filepath = regexp_replace(filepath, '^cor/', 'con/')
where filepath ~ '^cor/';

answered May 16, 2011 at 12:58

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges