UPDATE mysql database replace strings

Question

I have in my db strings like www.domain.com and http://www.domain.com. I want to prepend to all entries the http:// but not affect other urls and as a result have this: http://http://www.domain.com

Can this be done with mysql only? I have used REPLACE(field,'www','http://www'), but this replaces also the http://www with http://http://www

Thanks in advance

EDIT

I forgot to mention that in the field there might be entries which don't contain www or http://www and therefore I don't want to alter or maybe there are entries like <p><a href="www.domain.com/">domain</a></p> in which CONCAT() prepends the http:// before <p>

Derek Downey · Accepted Answer · 2011-11-08 16:47:50Z

1

Try adding a WHERE clause to your update to only update fields that do not already have 'http://'. Test it out like this

 SELECT CONCAT('http://', field) FROM foo WHERE LOCATE('http://', field)=0

and your UPDATE syntax would be:

UPDATE foo SET field=CONCAT('http://',field) WHERE LOCATE('http://', field)=0

answered Nov 8, 2011 at 16:47

Derek Downey

1,5322 gold badges10 silver badges17 bronze badges

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

1 Comment

Brad Christie Over a year ago

+1, LOCATE is also another viable option.

Brad Christie · Accepted Answer · 2011-11-08 16:49:13Z

0

I won't worry about performance as this seems like a one-off kind of script. That said, you can couple LEFT and CONCAT to achieve this:

UPDATE  mytable
SET     mycolumn = CONCAT('http://',mycolumn)
WHERE   LEFT(mycolumn,7) <> 'http://'

Do note that I'm not taking CapItaliZation in to account. You may also want to consider sanitizing the information either before adding it to the database, or maybe make a trigger to do it for you.

answered Nov 8, 2011 at 16:49

Brad Christie

102k16 gold badges160 silver badges200 bronze badges

Comments

Suresh Kamal · Accepted Answer · 2013-03-20 07:39:02Z

0

Search and Replace Query - mysql replace

Here is the SQL query to replace string in your MySQL database table:

UPDATE table_name SET column_name = REPLACE(column_name,'original_string','replace_string')

Here is what I did to change the path URLs in all my previous posts.

UPDATE `wp_posts` SET `post_content` = REPLACE(`post_content`,'http://localhost/','https://sureshkamal1.wordpress.com/')

answered Mar 20, 2013 at 7:39

Suresh Kamal

92 silver badges6 bronze badges

1 Comment

Hibou57 Over a year ago

Why no WHERE? Is this too much superfluous? Does it cost anything to add something like WHERE column_name LIKE CONCAT('%s', 'original_string', '%')? I wonder…

Collectives™ on Stack Overflow

UPDATE mysql database replace strings

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related