regex find-and-replace in Postgresql queries

postgres-regexp_replace.md

Regex replacements in postgres

I had to make a simple change to all the strings in a table, and I was dreading having to load them into memory, iterate over them, searching for the string, and updating replacements. So instead, I learned that postgresql can actually do regex replacements in an update statement.

For example, if I have a links table with a url column with a bunch of URLs erroneously ending in "?":

Link.where("long_url like '%?'").count #=> 487185

Then I can strip the trailing ? from those records with this query:

ActiveRecord::Base.connection.execute(
  "update links set long_url = regexp_replace(long_url,'\\?$','') where long_url ~ '\\?$'"
)

The command completed in about 20 seconds. To verify:

Link.where("long_url like '%?'").count #=> 0