Last active
August 28, 2020 17:18
-
-
Save devzom/baa131f6c48aab0a8c05281db45a7192 to your computer and use it in GitHub Desktop.
MySQL: Generate slug from ex. name
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
A slug is a short name using human-readable keywords to identify a web page. For example, in the following URL | |
http://www.example.com/schools/new-york-art-school | |
the slug is new-york-art-school. This post shows how to automatically generate slug using SQL in MySQL. | |
Suppose we have a table schools a field name, and we wish to generate a slug for each school using the name. First, add one field for the slug. | |
ALTER TABLE schools ADD slug VARCHAR(128) NULL; | |
Then, generate slugs using school names. | |
UPDATE schools SET | |
slug = replace(trim(lower(name)), ' ', '-'); | |
Use the following to double-check the slug has only alphabets, numbers or dashes. | |
SELECT * FROM schools WHERE | |
slug NOT RLIKE '^([a-z0-9]+\-)*[a-z0-9]+$'; | |
The following query helps to replace special characters like dots, quotes, etc. Sometimes its necessary to run multiple times to remove dashes. | |
UPDATE schools SET | |
slug = lower(name), | |
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, '--', '-'); | |
// | |
/* | |
* Polish special chars | |
*/ | |
-- slug = replace(slug, ' ć', ' c'), | |
-- slug = replace(slug, ' ś', ' s'), | |
-- slug = replace(slug, ' ż', ' z'), | |
-- slug = replace(slug, ' ź', ' z'), | |
-- slug = replace(slug, ' ą', ' a'), | |
-- slug = replace(slug, ' ę', ' e'), | |
-- slug = replace(slug, ' ł', ' l'), | |
-- slug = replace(slug, ' ó', ' o'), | |
-- slug = replace(slug, ' ń', ' n'), | |
/* | |
* Czech special chars | |
*/ | |
-- slug = replace(slug, ' ý', ' y'), | |
-- slug = replace(slug, ' ž', ' z'), | |
-- slug = replace(slug, ' €', ' E'), | |
-- slug = replace(slug, ' á', ' a'), | |
-- slug = replace(slug, ' č', ' c'), | |
-- slug = replace(slug, ' ď', ' d'), | |
-- slug = replace(slug, ' é', ' e'), | |
-- slug = replace(slug, ' ě', ' e'), | |
-- slug = replace(slug, ' ň', ' n'), | |
-- slug = replace(slug, ' ó', ' o'), | |
-- slug = replace(slug, ' ř', ' r'), | |
-- slug = replace(slug, ' š', ' s'), | |
-- slug = replace(slug, ' ť', ' s'), | |
-- slug = replace(slug, ' ú', ' u'), | |
-- slug = replace(slug, ' ů', ' u'), | |
UPDATE schools SET | |
slug = replace(slug, '--', '-'); | |
Finally, add unique key to the slug field. | |
ALTER TABLE schools ADD UNIQUE (slug); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment