Replace a substring within a column value for all rows with an SQL UPDATE query

Question

Here's a sample table:

name       |   picture

John S.    |   http://servera.host.com/johns.png
Linda B.   |   http://servera.host.com/lindab.png
...

Let's say there are several hundred more records.

Let's also say we moved servers from "servera" to "serverb".

Would it be possible to go into this table with one query to rename the content in the column "picture" for every record to read the correct server name?

Yes, on almost every database engine it would be possible to do this with a single UPDATE query. Since you don't specify a particular product, however, you're likely only to get answers for whatever someone's favorite product is. Let us know what you're using and someone will reply with the syntax for that product. — Larry Lustig
– Larry Lustig, Commented Oct 15, 2010 at 16:07
Oh, that's true - thanks, Larry. I'd forgotten to mention that I am developing on MySQL — Julian H. Lam
– Julian H. Lam, Commented Oct 15, 2010 at 16:13
This is more of an architectural sticking point than answering your question directly, but I would suggest not storing the FQDN of the url in your database. Keep that as a constant in your application code, and then when you draw a link to your picture the src will combine the PIC_HOST_DOMAIN_NAME.'/johns.png'. This makes it much easier to move your images around, as well as use a CDN as a CNAME sitting in front of your images. — David O.
– David O., Commented Oct 15, 2010 at 16:22
Hi David - I agree that that is the easiest and best way to go. Our more recent applications support just this philosophy, but our legacy apps do not. Hence the problem in the first place. — Julian H. Lam
– Julian H. Lam, Commented Oct 15, 2010 at 16:44

OMG Ponies · Accepted Answer · 2010-10-15 16:18:33Z

111

T-SQL:

update TBL 
   set picture = Replace(picture, 'servera', 'serverb') 
 where picture like '%servera%'

Oracle:

update TBL 
   set picture = replace(picture, 'servera', 'serverb') 
 where picture like '%servera%'

MySQL:

update TBL 
   set picture = REPLACE(picture, 'servera', 'serverb') 
 where picture like '%servera%'

edited Oct 15, 2010 at 16:18

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

answered Oct 15, 2010 at 16:09

Anatoly G

4,1522 gold badges23 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrew · Accepted Answer · 2010-10-15 16:15:18Z

20

UPDATE users
SET picture = REPLACE(picture, 'http://servera.host.com/', 'http://serverb.host.com/')
WHERE picture LIKE 'http://servera.host.com/%';

I'm including more of the string because I'd worry about 'fixing' an image named 'somethingserverasomething.jpg'. I might also think about having a base_url table and just storing image file names in users, but that's not the question you asked ;-)

answered Oct 15, 2010 at 16:15

Andrew

4,62429 silver badges31 bronze badges

Collectives™ on Stack Overflow

Replace a substring within a column value for all rows with an SQL UPDATE query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related