Skip to content

Instantly share code, notes, and snippets.

@exebetche
Created December 22, 2022 01:31
Show Gist options
  • Save exebetche/db329dd6635dc535d617c6dcbeda1b96 to your computer and use it in GitHub Desktop.
Save exebetche/db329dd6635dc535d617c6dcbeda1b96 to your computer and use it in GitHub Desktop.
DELIMITER ;;
DROP FUNCTION IF EXISTS `slugify`;;
CREATE FUNCTION `slugify`(`original` varchar(512) CHARACTER SET 'utf8mb4') RETURNS varchar(512) CHARSET ascii
BEGIN
DECLARE translit VARCHAR(512) DEFAULT '';
DECLARE len INT(3) DEFAULT 0;
DECLARE pos INT(3) DEFAULT 1;
DECLARE letter CHAR(6);
SET len = CHAR_LENGTH(original);
WHILE (pos <= len) DO
SET letter = SUBSTRING(original, pos, 1);
CASE TRUE
WHEN letter = 'a' THEN SET letter = 'a';
WHEN letter = 'b' THEN SET letter = 'b';
WHEN letter = 'c' THEN SET letter = 'c';
WHEN letter = 'd' THEN SET letter = 'd';
WHEN letter = 'e' THEN SET letter = 'e';
WHEN letter = 'f' THEN SET letter = 'f';
WHEN letter = 'g' THEN SET letter = 'g';
WHEN letter = 'h' THEN SET letter = 'h';
WHEN letter = 'i' THEN SET letter = 'i';
WHEN letter = 'j' THEN SET letter = 'j';
WHEN letter = 'k' THEN SET letter = 'k';
WHEN letter = 'l' THEN SET letter = 'l';
WHEN letter = 'ł' THEN SET letter = 'l';
WHEN letter = 'm' THEN SET letter = 'm';
WHEN letter = 'n' THEN SET letter = 'n';
WHEN letter = 'o' THEN SET letter = 'o';
WHEN letter = 'p' THEN SET letter = 'p';
WHEN letter = 'q' THEN SET letter = 'q';
WHEN letter = 'r' THEN SET letter = 'r';
WHEN letter = 's' THEN SET letter = 's';
WHEN letter = 't' THEN SET letter = 't';
WHEN letter = 'u' THEN SET letter = 'u';
WHEN letter = 'v' THEN SET letter = 'v';
WHEN letter = 'w' THEN SET letter = 'w';
WHEN letter = 'x' THEN SET letter = 'x';
WHEN letter = 'y' THEN SET letter = 'y';
WHEN letter = 'z' THEN SET letter = 'z';
WHEN letter = ' ' THEN SET letter = '-';
WHEN letter = '' THEN SET letter = '-';
ELSE SET letter = letter;
END CASE;
SET translit = CONCAT(translit, letter);
SET pos = pos + 1;
END WHILE;
SET translit = regexp_replace(translit, '[^a-z0-9]', '-');
SET translit = regexp_replace(translit, '^\-*', '');
SET translit = regexp_replace(translit, '\-*$', '');
SET translit = regexp_replace(translit, '\-+', '-');
RETURN translit;
END;;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment