SQL mass replace column value

Question

I have a database with a table name called mybb_users.

All users have a field name called avatar. Some users have their avatar set as

 http://graph.facebook.com/userid/picture?width=250&height=250.

I want to mass-replace all the width and height of the people that use a facebook picture with this amount of width and height. I unfortunately don't know how to do this since the userid is random. Is there anyway I can mass replace width=250&height=250 to width=140&height=140 ?

Thank you!

What you're looking for is the string function [REPLACE](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace). — Joachim Isaksson, Jun 08 '14 at 17:54

chresse · Accepted Answer · 2014-06-08T18:03:00.817

2

you can do it with the REPLACE function:

UPDATE mybb_users
SET avatar = REPLACE(`avatar`, 'width=250&height=250', 'width=140&height=140')
WHERE avatar like '%graph.facebook%'

for further information have a look at: http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_replace

edited Jun 08 '14 at 18:03

answered Jun 08 '14 at 17:57

chresse

5,486
3
30
47

Thank you for the quick respond, will this only overwrite that certain part? – user1978752 Jun 08 '14 at 18:00
yes. it only replaces `width=250&height=250` with `width=140&height=140` in the `avatar` column – chresse Jun 08 '14 at 18:01
Awesome it worked, thanks once again :) – user1978752 Jun 08 '14 at 18:02

score 0 · Answer 2 · answered Jun 08 '14 at 17:57

0

You can update just the portion you want like this:

UPDATE mybb_users SET avatar = REPLACE(avatar, 'width=250&height=250', 'width=140&height=140')

this should replace all occurences of the 250 with 140

answered Jun 08 '14 at 17:57

Silvertiger

1,680
2
19
32

SQL mass replace column value

2 Answers2