Vardchives

Search and Replace ​

Description ​

Find and replace a substring inside a column across many rows — stripping a stray ID out of URLs, fixing a changed domain, that sort of thing.

Snippet ​

sql
UPDATE listing_urls_queues
SET url = REPLACE(url, '725026567', '')
WHERE url LIKE '%725026567%';

Notes ​

Preview the damage first by running the same thing as a SELECT:

sql
SELECT url, REPLACE(url, '725026567', '') AS new_url
FROM listing_urls_queues
WHERE url LIKE '%725026567%';

The WHERE clause isn't strictly required — REPLACE leaves non-matching rows untouched — but keeping it means the update only writes the rows that actually change, instead of touching the whole table and bumping every row's updated_at.

REPLACE() is case-sensitive for binary collations and case-insensitive for _ci ones, so behaviour depends on the column's collation.