Due to a process issue (i.e. client not following instructions) I have over 900 urls with missing aliases in a mosets tree table.
If possible I would like to create an SQL query that will generate an alias from the
link_title by selecting, say, the first 3-5 words and obviously removing any numbers and special characters, replacing spaces with a - and making all characters lowercase.
My MySQL skills really aren't great, but so far I have created a select query for testing:
SELECT SUBSTRING_INDEX(`link_name`, ' ', 5) AS linkalias FROM `m5cob_mt_links`
Of course, I'd need to convert the finalised query into an update of some description. Could anyone provide any advice, or let me know if there is an easier way to populate all of the missing aliases?
Relevant portion of the SQL schema and data:
CREATE TABLE `m5cob_mt_links` ( `link_id` int(11) NOT NULL, `link_name` varchar(255) NOT NULL, `alias` varchar(255) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `m5cob_mt_links` (`link_id`, `link_name`, `alias`) VALUES (6, 'Convivio Bar & Restaurant Chester Cheshire UK', ''), (7, 'Slug and Lettuce Chester Cheshire UK', ''), (8, 'Da Rosa Restaurant & Wine Bar Torrevieja Costa Blanca Spain', ''), (9, 'Bar Lounge Chester Cheshire UK', ''), (10, 'Telfords Warehouse Chester Cheshire UK', ''), (11, 'The Botanist Chester Cheshire UK', ''), (12, 'Missoula Chester Cheshire UK', ''), (13, 'Fiesta Havana Chester Cheshire UK', ''), (14, 'El Oceano Hotel Restaurant, Torrenueva, Marbella, Malaga', ''), (15, 'La Plaza Restaurant Riviera del Sol Mijas Costa Malaga Spain', '');
So as an example, the first row of data has a
Convivio Bar & Restaurant Chester Cheshire UK. With the query, I would like to populate the
convivio-bar-restaurant-chester-cheshire thus taking the first 5 words, removing the capitalisation and punctuation and replacing the spaces with '-'.