How to generate slug url based on fields in SQL MySql
Posted: Sun Dec 31, 2017 4:11 pm
1. Generate and set slug. In this example, couple of fields are concatenated with a character in middle '-'.
2. Run again
3. Use the following to double-check the slug has only alphabets, numbers or dashes.
Correct as needed
4. Confirm unique slug
Code: Select all
UPDATE `table_name` SET
`slug` = lower(concat(`field1`, '-', `field2`)),
`slug` = replace(`slug`, '.', ' '),
`slug` = replace(`slug`, ',', ' '),
`slug` = replace(`slug`, ';', ' '),
`slug` = replace(`slug`, ':', ' '),
`slug` = replace(`slug`, '?', ' '),
`slug` = replace(`slug`, '%', ' '),
`slug` = replace(`slug`, '&', ' '),
`slug` = replace(`slug`, '#', ' '),
`slug` = replace(`slug`, '*', ' '),
`slug` = replace(`slug`, '!', ' '),
`slug` = replace(`slug`, '_', ' '),
`slug` = replace(`slug`, '@', ' '),
`slug` = replace(`slug`, '+', ' '),
`slug` = replace(`slug`, '(', ' '),
`slug` = replace(`slug`, ')', ' '),
`slug` = replace(`slug`, '[', ' '),
`slug` = replace(`slug`, ']', ' '),
`slug` = replace(`slug`, '/', ' '),
`slug` = replace(`slug`, '-', ' '),
`slug` = replace(`slug`, '\'', ''),
`slug` = trim(`slug`),
`slug` = replace(`slug`, ' ', '-'),
`slug` = replace(`slug`, '--', '-');
Code: Select all
UPDATE `table_name` SET `slug` = replace(`slug`, '--', '-');
Code: Select all
SELECT * FROM `table_name` WHERE `slug` NOT RLIKE '^([a-z0-9]+\-)*[a-z0-9]+$';
4. Confirm unique slug
Code: Select all
SELECT COUNT(*) FROM `sma_categories` GROUP BY `slug` HAVING COUNT(*) > 1