How to generate slug url based on fields in SQL MySql

Post Reply
User avatar
Saman
Lieutenant Colonel
Lieutenant Colonel
Posts: 828
Joined: Fri Jul 31, 2009 10:32 pm
Location: Mount Lavinia

How to generate slug url based on fields in SQL MySql

Post by Saman » 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 '-'.

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`, '--', '-');
2. Run again

Code: Select all

UPDATE `table_name` SET `slug` = replace(`slug`, '--', '-');
3. Use the following to double-check the slug has only alphabets, numbers or dashes.

Code: Select all

SELECT * FROM `table_name` WHERE `slug` NOT RLIKE '^([a-z0-9]+\-)*[a-z0-9]+$';
Correct as needed

4. Confirm unique slug

Code: Select all

SELECT COUNT(*) FROM `sma_categories` GROUP BY `slug` HAVING COUNT(*) > 1
Post Reply

Return to “PHP & MySQL”