Slugify
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'), # Occured in Summer 2019 in the wild: Curaçao
'è', '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