Slug (MySQL)

Uit De Vliegende Brigade
Ga naar: navigatie, zoeken

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

Zie ook

Bronnen