MySQL RegExp Replace [closed]

Question

Here is my situation:

My company hosts multiple sites for clients, and the content of the sites are stored in a MySQL database.

I have an issue where I need to go through the whole database and replace pieces of the content. For example:

I need to replace http://downloads.mysever.com/siteID/someImage.jpg with //downloads.mysever.com/siteID/someImage.jpg

So I just need to remove the http: from all links that are going to our downloads server.

My question is what would be the best way to go about this? Will I need to write a php script to handle this, or am I able to use just MySQL to accomplish this.

Thanks

There is no "best way" to do something so trivial. Use whatever tools you are most comfortable with. — user229044
– user229044 ♦, Commented Dec 19, 2012 at 16:29
So is this possible just using MySQL? I wasn't sure if regular expressions were possible in MySQL. I would prefer to write a query that would be able to handle it, but if needed I can write a PHP script. — noub
– noub, Commented Dec 19, 2012 at 16:31
@noub Why would you need a regex? You're just stripping the first 5 chars. At worst just find/replace http: with an empty string, still doesn't require a regex — DaveRandom
– DaveRandom, Commented Dec 19, 2012 at 16:32
Regular expressions are not a magic wand that is the best solution to every problem that relates to strings. — Andy Lester
– Andy Lester, Commented Dec 19, 2012 at 16:56

Community · Accepted Answer · 2017-05-23 11:58:19Z

2

you could do this with pure mysql:

update table set field = REPLACE(field, 'http:', '') where INSTR(field, 'http:') > 0;

see this question

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Dec 19, 2012 at 16:33

keune

5,8054 gold badges39 silver badges53 bronze badges

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

1 Comment

hek2mgl Over a year ago

Note that the WHERE condition is not required

hek2mgl · Accepted Answer · 2012-12-19 16:39:42Z

2

you can use the sql statement

 UPDATE tablename SET url = REPLACE(url, 'http:', '');

Read this blog post

You can execute this command using the command line

mysql -uUSERNAME -p DATABASE_NAME -e "UPDATE tablename SET url = REPLACE(url, 'http:', '')";

edited Dec 19, 2012 at 16:39

answered Dec 19, 2012 at 16:32

hek2mgl

159k31 gold badges263 silver badges279 bronze badges

Comments

Sam Dufel · Accepted Answer · 2012-12-19 16:31:50Z

0

If you're able to take the database offline briefly, I've found the easiest way is to:

use mysqldump to export the entire database
run a find-and-replace in whichever editor you prefer to update the database dump
recreate the database from the dump file - make sure it all went correctly, then replace the original database with the new version.

answered Dec 19, 2012 at 16:31

Sam Dufel

17.6k3 gold badges51 silver badges52 bronze badges

Comments

Mike Brant · Accepted Answer · 2012-12-19 16:42:37Z

0

MySQL does offer regular expression functionality. The functionality is probably too detailed to go into here so here is the link:

http://dev.mysql.com/doc/refman/5.5/en/regexp.html

A basic usage example for you sample case would be:

UPDATE table SET field = REPLACE(field, 'http:', '') WHERE field REGEXP '^http:'

Note however that this case could be done without regular expression by simply doing this:

UPDATE table SET field = REPLACE(field, 'http:', '') WHERE field LIKE 'http:%'

So you might not need to use regular expressions for each type of replacement you want to do.

answered Dec 19, 2012 at 16:42

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Comments

spencer7593 · Accepted Answer · 2012-12-19 16:53:36Z

To remove the leading 'http:' string (from a character type column) of all rows in a table, then something as simple as this will work:

UPDATE mytable
   SET mycol = SUBSTRING(mycol,6)
 WHERE mycol LIKE 'http:%'

UPDATE:

Note that using the REPLACE function will replace ALL occurrences of the specified string from a string; it may not be appropriate for removing just the leading occurrence of a specified string.

If you want to remove ALL occurrences of 'http:' found within column values, then:

UPDATE mytable
   SET mycol = REPLACE( mycol , 'http:, '')
 WHERE mycol LIKE '%http:%'

MySQL does provide support for regular expressions, specifically, for finding values that match a given regular expression; but MySQL does not provides support for "replacement" using a regular expression.

So you could use this in a query:

 WHERE mycol REGEXP 'http:'

in place of (equivalent to):

 WHERE mycol LIKE '%http:%'

Collectives™ on Stack Overflow

MySQL RegExp Replace [closed]

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Linked

Related