I have a table in a MySQL Server 5.7 database. This table has a varchar(256) column called ‘url’. Unfortunately,
The values in this field have a path included and not to be included when importing into a WordPress blog. For instance the url 'time-for-land-audit' is actually stored as
'**/articles/2013/02/27/time-for-land-audit**' in the table.
I want to update all of the records in this table to remove the date format
(**/articles/YYYY/MM/DD/**).
How do I update all of the records at once to remove the extra spaces?
Advertisement
Answer
mysql> SELECT SUBSTRING_INDEX('/articles/2013/02/27/time-for-land-audit', '/', -1) AS short_url;
+---------------------+
| short_url |
+---------------------+
| time-for-land-audit |
+---------------------+
As an UPDATE it would look like this:
UPDATE mytable SET url = SUBSTRING_INDEX(url, '/', -1)
See https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index for documentation.
P.S.: Be sure to test that this does what you want on a test sample of the data first! You wouldn’t want to be surprised and find that you had ruined your sole copy of the data.