MySQL: Generate slug from ex. name

MySQL: Generate slug from ex. name.txt

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);