Slugify
Versie door Jeroen Strompf (overleg | bijdragen) op 29 mei 2019 om 16:20
Hoe fiets je een string om naar iets dat geschikt is als slug of bestandsnaam?
Voorbeelden
[1]:
CREATE FUNCTION toSlug(s NVARCHAR(500)) RETURNS NVARCHAR(500) DETERMINISTIC RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(LOWER(TRIM(s)), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '/', ''), '"', ''), '?', ''), "'", ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-'),'ù','u'), 'ú','u'),'û','u'),'ü','u'),'ý','y'),'ë','e'),'à','a'),'á','a'),'â','a'),'ã','a'), 'ä','a'),'å','a'),'æ','a'),'ç','c'),'è','e'),'é','e'),'ê','e'),'ë','e'),'ì','i'), 'í','i'),'ě','e'), 'š','s'), 'č','c'),'ř','r'), 'ž','z'), 'î','i'),'ï','i'),'ð','o'), 'ñ','n'),'ò','o'),'ó','o'),'ô','o'),'õ','o'),'ö','o'),'ø','o'),'%', '');
[2]:
LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM('My String'), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\"', ''), '?', ''), '\'', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-')) AS `post_name`
Mijn oplossing
Met dank aan de eerste oplossing hierboven:
CREATE DEFINER=`root`@`localhost` FUNCTION `slug`(s nvarchar(500)) RETURNS varchar(500) CHARSET utf8 DETERMINISTIC BEGIN # Convert a text string to a slug (within a certain interpretaton ########################################################################################## # # * Without conversion to lowercase # * "Replace" replaces all instances of a given symbol - No need to replace a certain # symbol multiple times # * There's room for lots of other symbols. Maybe a shortcut: Convert the input string to # a more restricted character encoding first, and than filter the remaining stuff # * Source: http://wiki.devliegendebrigade.nl/Slug_(MySQL) # * Lost count of the number of braces? Just add or delete some, until the error # disappears ;) # return # Opening braces 1-50 ##################### # replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( replace(replace(replace(replace(replace(replace(replace(replace(replace(replace( # Remaining opening braces ########################## # replace(replace(replace( trim(s), # Do these as last (in the outer shells) ############################################## # # * To catch double dashes, earlier converted from string # * This works for up to 6 spaces in a row. If input strings can contain more than # 6 spaces in a row: Run this function multiple times on the same string # '--', '-'), '--', '-'), '--', '-'), # Now the usual stuff ############################################## # "'", ''), ' ', '-'), # Space to normal dash. Not to underscore '!', ''), '"', ''), '%', ''), '&', ''), '(', ''), ')', ''), ',', ''), # Controversial to eliminate commas? ', ', ''), '.', ''), '/', ''), ':', ''), '?', ''), '\\', ''), 'à', 'a'), 'á', 'a'), 'â', 'a'), 'ã', 'a'), 'ä', 'a'), 'å', 'a'), 'æ', 'ae'), 'ç', 'c'), 'è', 'e'), 'é', 'e'), 'ê', 'e'), 'ë', 'e'), 'ë', 'e'), 'ì', 'i'), 'í', 'i'), 'î', 'i'), 'ï', 'i'), 'ð', 'd'), 'ñ', 'n'), 'ò', 'o'), 'ó', 'o'), 'ô', 'o'), 'õ', 'o'), 'ö', 'o'), 'ø', 'o'), 'ù', 'u'), 'ú', 'u'), 'û', 'u'), 'ü', 'u'), 'ý', 'y'), 'č', 'c'), 'ě', 'e'), 'ř', 'r'), 'š', 's'), 'ž', 'z'); END
Ongetwijfeld is er ruimte voor nog meer vreemde symbolen. Daarnaast kan het misschien helpen om de input-string eerst om te fietsen naar een eenvoudigere karaktercodering. En het zou zelfs nog iteratief kunnen, maar dat lijkt me overkill