gefvert.org

MySQL Function for Making URL Names

2020-09-03

This function takes a text like "I Can't Cope with Socialists!" and transforms it to "i-cant-cope-with-socialists", suitable for URL names and similar. Preserves only letters (A-Z) and digits, removes double hyphens and makes everything lowercase.

CREATE FUNCTION mangle_name (str VARCHAR(255))
    RETURNS VARCHAR(255)
    LANGUAGE SQL
    DETERMINISTIC
    NO SQL
    SQL SECURITY INVOKER
    COMMENT 'Generates a database name from a text, i.e. removing all noncharacters and adding hyphens instead of spaces'
BEGIN
    SET str = REGEXP_REPLACE(LOWER(str), '[\']+', '');
    SET str = REGEXP_REPLACE(LOWER(str), '[^a-z0-9]+', '-');

    WHILE LOCATE('--', str) <> 0 DO
        SET str = REPLACE(str, '--', '-');
    END WHILE;

    RETURN TRIM(BOTH '-' FROM str);
END