Use regular expressions to edit an entire column with MySQL

I have a table like so:

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

But, I need to change it so it contains:

| link  |
----------
http://site.com/resources/images/image1.png
http://site.com/resources/images/image2.png
http://site.com/resources/images/image3.png
http://site.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?

Answers


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'

Need Your Help

FRP on a game engine. Is it worth it?

c++ architecture components game-engine frp

Today, I've read about FRP (functional reactive programming). However, I don't know how much this fits in the engine itself.

how to use the image file located in local drive, for printing it in jsp page or servlet page?

java jsp servlets

I want to display the image located in my local drive. I am using sun java app server 8. For example If I generate a file abc.jpg dynamically and store it in c:\abc.jpg, then how could I use it in ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.