Trim unwanted text with start and end index in column

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?

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.