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