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'),
'è',	'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

Zie ook

Bronnen