CODEWITHSUNDEEP
mysqlsqlregex
425nesp
425nesp

Reputation: 7583

Use regular expressions to edit an entire column with MySQL

I have a table like so:

| link  |
----------
http://dev.example.com/images/image1.png
http://dev.example.com/images/image2.png
http://dev.example.com/images/image3.png
http://dev.example.com/images/image4.png

But, I need to change it so it contains:

| link  |
----------
http://example.com/resources/images/image1.png
http://example.com/resources/images/image2.png
http://example.com/resources/images/image3.png
http://example.com/resources/images/image4.png

There are lots of entries that need changing. What would be a good way to go about updating all of the entries?

Right now I'm using this to find the entries:

SELECT column
FROM table
WHERE link REGEXP '^dev'

I was updating the links by hand first, but there are so many left. I can't update them all myself.

Any ideas?

Upvotes: 0

Views: 100

Answers (1)

gillyspy
gillyspy

Reputation: 1598

You call it "Field" in the table listing but then "link" in the SQL so i'm going to use "Field"

update table 
set 
    Field = replace(Field, 'dev.site.com', 'site.com/resources') 
where 
    Field rlike '^dev.site.com'

Upvotes: 2

Related Questions