root@localhost [test]> CREATE TABLE `forumpost` (
-> `name` varchar(255) DEFAULT NULL
-> ) ENGINE=InnoDB;
root@localhost [test]> insert into forumpost value ('An auditor drives an audi.'),('An auditor drives a volvo.');
root@localhost [test]> select * from forumpost;
+----------------------------+
| name |
+----------------------------+
| An auditor drives an audi. |
| An auditor drives a volvo. |
+----------------------------+
root@localhost [test]>UPDATE forumpost SET name = REPLACE(name, 'audi.', 'toyota.') WHERE name LIKE '%audi.';
Query OK, 1 row affected (0.20 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost [test]> select * from forumpost;
+------------------------------+
| name |
+------------------------------+
| An auditor drives an toyota. |
| An auditor drives a volvo. |
+------------------------------+
root@localhost [test]> insert into forumpost value ('An auditor drives an audi.');
root@localhost [test]> insert into forumpost value ('An auditor drives an audi car');
root@localhost [test]> select * from forumpost;
+-------------------------------+
| name |
+-------------------------------+
| An auditor drives an toyota. |
| An auditor drives a volvo. |
| An auditor drives an audi. |
| An auditor drives an audi car |
+-------------------------------+
root@localhost [test]> SELECT * FROM forumpost WHERE name REGEXP 'audi car$';
+-------------------------------+
| name |
+-------------------------------+
| An auditor drives an audi car |
+-------------------------------+
root@localhost [test]> SELECT * FROM forumpost WHERE name LIKE '%audi car%';
+-------------------------------+
| name |
+-------------------------------+
| An auditor drives an audi car |
+-------------------------------+
root@localhost [test]> SELECT SUBSTRING(name,-8,4), name FROM forumpost WHERE SUBSTRING(name,-8,4) = 'audi';
+----------------------+-------------------------------+
| SUBSTRING(name,-8,4) | name |
+----------------------+-------------------------------+
| audi | An auditor drives an audi car |
+----------------------+-------------------------------+
root@localhost [test]> insert into forumpost value ('An auditor drives an red audi car');
Query OK, 1 row affected (0.04 sec)
root@localhost [test]> select * from forumpost;
+------------------------------------+
| name |
+------------------------------------+
| An auditor drives an toyota. |
| An auditor drives a volvo. |
| An auditor drives an audi. |
| An auditor drives an audi car |
| An auditor drives an audi blue car |
| An auditor drives an red audi car |
+------------------------------------+
root@localhost [test]> SELECT LOCATE('audi', name,8), name FROM forumpost WHERE LOCATE('audi', name,8) > 0 ;
+------------------------+------------------------------------+
| LOCATE('audi', name,8) | name |
+------------------------+------------------------------------+
| 22 | An auditor drives an audi. |
| 22 | An auditor drives an audi car |
| 22 | An auditor drives an audi blue car |
| 26 | An auditor drives an red audi car |
+------------------------+------------------------------------+
UPDATE forumpost SET name = REPLACE(name, LOCATE('audi', name,8), 'mercedes') WHERE LOCATE('audi', name,8) > 0 ;
Query OK, 0 rows affected (0.02 sec)
Rows matched: 4 Changed: 0 Warnings: 0
SELECT name , CONCAT ( SUBSTRING_INDEX(name, 'audi', 2) , ' mercedes ' , SUBSTRING_INDEX(name, 'audi', -1) ) as newvalue
FROM forumpost
WHERE LOCATE('audi', name,10) > 0 ;
+-----------------------------------+-----------------------------------------+
| name | newvalue |
+-----------------------------------+-----------------------------------------+
| An auditor drives an audi. | An auditor drives an mercedes . |
| An auditor drives an audi. | An auditor drives an mercedes . |
| An auditor drives an audi car | An auditor drives an mercedes car |
| An auditor drives an red audi car | An auditor drives an red mercedes car |
+-----------------------------------+-----------------------------------------+
root@localhost [test]> UPDATE forumpost SET name = CONCAT(SUBSTRING_INDEX(name, 'audi', 2) , ' mercedes ' , SUBSTRING_INDEX(name, 'audi', -1) )
WHERE LOCATE('audi', name,10) > 0 ;
Query OK, 4 rows affected (0.03 sec)
Rows matched: 4 Changed: 4 Warnings: 0
root@localhost [test]> select * from forumpost;
+-----------------------------------------+
| name |
+-----------------------------------------+
| An auditor drives an mercedes . |
| An auditor drives a volvo. |
| An auditor drives an mercedes . |
| An auditor drives an mercedes car |
| An auditor drives an red mercedes car |
+-----------------------------------------+
5 rows in set (0.00 sec)