CODEWITHSUNDEEP
mysql
PCC
PCC

Reputation: 73

MySQL regex style replace?

I have a column in a table that contains the path for a file, along the lines of:

/here/here2/something.jpg

I need to change every row to map it to:

/here3/something.jpg

Is there an easy way to do this? Thanks!

Upvotes: 1

Views: 164

Answers (2)

Asaph
Asaph

Reputation: 162851

You don't need regex for this. Simple string functions can do the trick. You could use a query like this:

update test set path=concat('/here3/',
    substring(path, length('/here/here2/') + 1))
    where path like '/here/here2/%';

Here is a little test case to prove it works:

mysql> create table test (path varchar(64));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test (path) values
    -> ('/here/here2/something.jpg'),
    -> ('/here/here2/something-else.jpg'),
    -> ('/here/here2/yet-another-something.jpg');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+---------------------------------------+
| path                                  |
+---------------------------------------+
| /here/here2/something.jpg             |
| /here/here2/something-else.jpg        |
| /here/here2/yet-another-something.jpg |
+---------------------------------------+
3 rows in set (0.00 sec)

mysql> update test set path=concat('/here3/',
    -> substring(path, length('/here/here2/') + 1))
    -> where path like '/here/here2/%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from test;
+----------------------------------+
| path                             |
+----------------------------------+
| /here3/something.jpg             |
| /here3/something-else.jpg        |
| /here3/yet-another-something.jpg |
+----------------------------------+
3 rows in set (0.00 sec)

Upvotes: 1

blake305
blake305

Reputation: 2236

First of all, are you familiar with using PHP? You could write a script to get all of the entries from the database, and then change them using preg_replace(). Then you could use mysql to update the database.

Upvotes: 0

Related Questions