How to generate SLUG URL in MYSQL

How to generate SLUG URL in MYSQL 

A slug is a brief term that identifies a web page by using human-readable keywords. As an illustration, the following URL

http://www.notes2free.com/blogs/play-school

Play-school is the slug. This article demonstrates how to use SQL in MySQL to automatically generate slugs.

Let`s say we want to create a slug for each school using the field name from the school`s table. Add a field for the slug first.

ALTER TABLE blogs 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 remo ve 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, `--`, `-`);

UPDATE schools SET slug = replace(slug, `--`, `-`);

Finally, add a unique key to the slug field.

ALTER TABLE schools ADD UNIQUE (slug);