ralphilius
8/3/2014 - 3:23 PM

gistfile1.txt

# http://theoryapp.com/generate-slug-url-in-mysql/
#This checks text with special characters
SELECT * FROM schools WHERE
    slug NOT RLIKE '^([a-z0-9]+\-)+[a-z0-9]+$';

#This updates special characters with dash
UPDATE reddit SET
    title_slug = lower(title),
    title_slug = replace(title_slug, '.', ' '),
    title_slug = replace(title_slug, '\'', ''),
    title_slug = replace(title_slug, '[', ' '),
    title_slug = replace(title_slug, ']', ' '),
    title_slug = replace(title_slug, '(', ' '),
    title_slug = replace(title_slug, ')', ' '),
    title_slug = replace(title_slug, '!', ' '),
    title_slug = replace(title_slug, '@', ' '),
    title_slug = replace(title_slug, '$', ' '),
    title_slug = replace(title_slug, '%', ' '),
    title_slug = replace(title_slug, '^', ' '),
    title_slug = replace(title_slug, '&', ' '),
    title_slug = replace(title_slug, '#', ' '),
    title_slug = replace(title_slug, '/', ' '),
    title_slug = replace(title_slug, '<', ' '),
    title_slug = replace(title_slug, '>', ' '),
    title_slug = replace(title_slug, '+', ' '),
    title_slug = replace(title_slug, '?', ' '),
    title_slug = replace(title_slug, ':', ' '),
    title_slug = replace(title_slug, ',', ' '),
    title_slug = replace(title_slug, ';', ' '),
    title_slug = replace(title_slug, '*', ' '),
    title_slug = replace(title_slug, '~', ' '),
    title_slug = trim(title_slug),
    title_slug = replace(title_slug, ' ', '-'),
    title_slug = replace(title_slug, '--', '-');
UPDATE reddit SET
    title_slug = replace(title_slug, '--', '-');