CODEWITHSUNDEEP
postgresql
simon
simon

Reputation: 6117

Postgres: edit values with regex?

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!

Upvotes: 1

Views: 176

Answers (2)

user330315
user330315

Reputation:

Regular expressions aren't really needed:

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

Upvotes: 1

Denis de Bernardy
Denis de Bernardy

Reputation: 78443

There's a regexp_replace() function:

http://www.postgresql.org/docs/current/static/functions-string.html

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

Upvotes: 1

Related Questions