Skip to content

Instantly share code, notes, and snippets.

@AndersonNascimentoDosSantos
Last active August 27, 2023 18:25
Show Gist options
  • Save AndersonNascimentoDosSantos/220e04be960187b9249b3db05ea02bdd to your computer and use it in GitHub Desktop.
Save AndersonNascimentoDosSantos/220e04be960187b9249b3db05ea02bdd to your computer and use it in GitHub Desktop.
convert title into slug directly in the mySQL 8
DELIMITER //
CREATE FUNCTION SlugifyTitle(title VARCHAR(255), sep CHAR(1))
RETURNS VARCHAR(255)
BEGIN
SET title = REPLACE(CONVERT(title USING utf8mb4),'š', 's');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Đ', 'Dj');
SET title = REPLACE(CONVERT(title USING utf8mb4),'đ', 'dj');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ž', 'Z');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ž', 'z');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Č', 'C');
SET title = REPLACE(CONVERT(title USING utf8mb4),'č', 'c');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ć', 'C');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ć', 'c');
SET title = REPLACE(CONVERT(title USING utf8mb4),'À', 'A');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Á', 'A');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Â', 'A');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ã', 'A');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ä', 'A');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Å', 'A');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Æ', 'A');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ç', 'C');
SET title = REPLACE(CONVERT(title USING utf8mb4),'È', 'E');
SET title = REPLACE(CONVERT(title USING utf8mb4),'É', 'E');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ê', 'E');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ë', 'E');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ì', 'I');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Í', 'I');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Î', 'I');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ï', 'I');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ñ', 'N');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ò', 'O');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ó', 'O');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ô', 'O');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Õ', 'O');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ö', 'O');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ø', 'O');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ù', 'U');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ú', 'U');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Û', 'U');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ü', 'U');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ý', 'Y');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Þ', 'B');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ß', 'Ss');
SET title = REPLACE(CONVERT(title USING utf8mb4),'à', 'a');
SET title = REPLACE(CONVERT(title USING utf8mb4),'á', 'a');
SET title = REPLACE(CONVERT(title USING utf8mb4),'â', 'a');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ã', 'a');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ä', 'a');
SET title = REPLACE(CONVERT(title USING utf8mb4),'å', 'a');
SET title = REPLACE(CONVERT(title USING utf8mb4),'æ', 'a');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ç', 'c');
SET title = REPLACE(CONVERT(title USING utf8mb4),'è', 'e');
SET title = REPLACE(CONVERT(title USING utf8mb4),'é', 'e');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ê', 'e');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ë', 'e');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ì', 'i');
SET title = REPLACE(CONVERT(title USING utf8mb4),'í', 'i');
SET title = REPLACE(CONVERT(title USING utf8mb4),'î', 'i');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ï', 'i');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ð', 'o');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ñ', 'n');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ò', 'o');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ó', 'o');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ô', 'o');
SET title = REPLACE(CONVERT(title USING utf8mb4),'õ', 'o');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ö', 'o');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ø', 'o');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ù', 'u');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ú', 'u');
SET title = REPLACE(CONVERT(title USING utf8mb4),'û', 'u');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ý', 'y');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ý', 'y');
SET title = REPLACE(CONVERT(title USING utf8mb4),'þ', 'b');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ÿ', 'y');
SET title = REPLACE(CONVERT(title USING utf8mb4),'Ŕ', 'R');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ŕ', 'r');
SET title = REPLACE(CONVERT(title USING utf8mb4),'/', '-');
SET title = REPLACE(CONVERT(title USING utf8mb4),' ', '-');
SET title = REPLACE(CONVERT(title USING utf8mb4),',', '-');
SET title = REPLACE(CONVERT(title USING utf8mb4),'.', '-');
SET title = REPLACE(CONVERT(title USING utf8mb4),"'", '-');
SET title = REPLACE(CONVERT(title USING utf8mb4),';', '');
SET title = REPLACE(CONVERT(title USING utf8mb4),'"', '');
SET title = REPLACE(CONVERT(title USING utf8mb4),'!', '');
SET title = REPLACE(CONVERT(title USING utf8mb4),'?', '');
SET title = REPLACE(CONVERT(title USING utf8mb4),'º', 'o');
SET title = REPLACE(CONVERT(title USING utf8mb4),'ª', 'a');
SET title = REPLACE(CONVERT(title USING utf8mb4),"'", '');
SET title = REPLACE(CONVERT(title USING utf8mb4),'(', '');
SET title = REPLACE(CONVERT(title USING utf8mb4),')', '');
-- SET title = REPLACE(CONVERT(title USING utf8mb4),'R$', 'real');
SET title = REPLACE(CONVERT(title USING utf8mb4),'$', 'usd');
SET title = REPLACE(CONVERT(title USING utf8mb4),'|', '');
SET title = REPLACE(CONVERT(title USING utf8mb4),'%', '');
SET title = REPLACE(CONVERT(title USING utf8mb4),'¨', '');
SET title = REPLACE(CONVERT(title USING utf8mb4),'&', '');
SET title = REPLACE(CONVERT(title USING utf8mb4),'*', '');
SET title = REPLACE(CONVERT(title USING utf8mb4),'@', '');
SET title = REPLACE(CONVERT(title USING utf8mb4),'=', '');
SET title = REPLACE(CONVERT(title USING utf8mb4),'+', '');
-- Remover caracteres não permitidos
SET title = REGEXP_REPLACE(title, '[^ -~]', '');
-- Substituir separadores e caracteres especiais por sep
SET title = REGEXP_REPLACE(title, '[-_]+', sep);
-- Verificar se o título resultante é apenas um hífen
-- Verificar se o título resultante é apenas um hífen
IF title = '-' AND title NOT REGEXP '^[0-9]+(\\.[0-9]+)?$' THEN
SET title = '0';
END IF;
-- Remover espaços e separadores extras
SET title = REGEXP_REPLACE(title, CONCAT('[\\s' , sep , '&&[^0]]+'), sep);
-- Remover separadores no início ou final
SET title = TRIM(BOTH sep FROM title);
RETURN LOWER(title);
END;
//
DELIMITER ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment