MySQL to convert a string into a slug

gistfile1.txt

LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM('My String'), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\"', ''), '?', ''), '\'', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-')) AS `post_name`

You saved my life, Thank you infinitely

Thanks, Man XD

Works great, I only added replace for special chars from eastern Europe letters to normal (for example š => s). Thank you!

It helps a lot. Thanks.

Had some newline issues in some data. Might be useful to someone else to have a REPLACE(FIELD, '\n', '') in there as well.

Thanks!

Saved me hours..thanks!

Updated with new symbol - ’:

UPDATE table SET domain = LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(name), ':', ''), '’', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\"', ''), '?', ''), '\'', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-'))

Updated version it removes accents. Based on RafaSashi's answer from http://stackoverflow.com/questions/2753422/mysql-replace-accented-characters

SELECT LOWER(REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(  
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(  
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM('My String with accents like á é í ó ú'), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\"', ''), '?', ''), '\'', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-'),'ù','u'),'ú','u'),'û','u'),'ü','u'),'ý','y'),'ë','e'),'à','a'),'á','a'),'â','a'),'ã','a'), 
'ä','a'),'å','a'),'æ','a'),'ç','c'),'è','e'),'é','e'),'ê','e'),'ë','e'),'ì','i'),'í','i'), 
'î','i'),'ï','i'),'ð','o'),'ñ','n'),'ò','o'),'ó','o'),'ô','o'),'õ','o'),'ö','o'),'ø','o')) AS `post_name` 

Updated version with Czech accent chars like ě,š,č,ř,ž,í

LOWER(REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(REPLACE(
REPLACE( REPLACE( REPLACE( REPLACE(REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE( REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(value),
':', ''), ')', ''), '(', ''), ',', ''), '\', ''), '/', ''), '"', ''), '?', ''),
''', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-'),'ù','u'),
'ú','u'),'û','u'),'ü','u'),'ý','y'),'ë','e'),'à','a'),'á','a'),'â','a'),'ã','a'),
'ä','a'),'å','a'),'æ','a'),'ç','c'),'è','e'),'é','e'),'ê','e'),'ë','e'),
'ì','i'),'í','i'), 'î','i'),'ï','i'),'ð','o'),'ñ','n'),'ò','o'),'ó','o'),
'ô','o'),'ä','a'),'å','a'),'æ','a'),'ç','c'),'è','e'),'é','e'),'ê','e'),
'ë','e'),'ì','i'),'í','i'),'ě','e'), 'š','s'), 'č','c'), 'ř','r'), 'ž','z'),
'õ','o'),'ö','o'),'ø','o')) AS post_name

Fixed with replacing of %, replacing in LOWERCASE and escaping \ during replacement

SET cSeo = REPLACE(REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 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(cName)), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '/', ''), '"', ''), '?', ''), "'", ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-'),'ù','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'),'%', '')

Fixed for turkish characters

p.slug = CONCAT(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 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(pd.name)),
':', ''), ')', ''), '(', ''), ',', ''), '\', ''), '/', ''), '"', ''), '?', ''), "'", ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-'),'ù','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')
,'%', '')
,'ç', 'c')
,'ü', 'u')
,'ğ', 'g')
,'ş', 's')
,'ı', 'i')
,'.', '')
,'ö', 'ö')
,'ç', 'c')
,'#x27;', '')
,'&', ''),"-",p.product_id)

And this is why I've abandoned SQL.

If someone struggle with syntax error, you might need to escape the back slash ...,'\\', '')

really great

Hello,

Thank You for sharing this little helper.
Included polish chars below:

LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(attractions.title), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\"', ''), '?', ''), '\'', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-'), 'ą', 'a'), 'ż', 'z'), 'ź', 'z'), 'ć', 'c'), 'ń', 'n'), 'ł', 'l'), 'ó', 'o'), 'ę', 'e'), 'ś', 's'))

I would really like to tell you all that you are sluggifyin Ðð Þþ Ææ completly wrong

• Ð -> D
• ð -> d
• Þ -> Th
• þ -> th
• Æ -> AE
• æ -> ae

Please fix!
This is actually causing problems for some Icelandic people and tourists visiting Iceland (half of which are from USA) because place names and people's names are being slugified incorrectly.

Included Turkish chars
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(TRIM(pd.name)),' ','-'), 'ç', 'c'),'ü', 'u'),'ğ', 'g'),'ş', 's'),'ı', 'i'),'ö', 'o'),'ç', 'c')

SELECT LOWER(REGEXP_REPLACE(nama, '['"?:,./\&! ]+', '-'))`
You can add any characters between brackets

saved me lots of time

Laravel / PHP
$sql = 'UPDATE citiesSETslug = LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(name), \':\', \'\'), \'’\', \'\'), \')\', \'\'), \'(\', \'\'), \',\', \'\'), \'\\\\\', \'\'), \'\\/\', \'\'), \'\\\"\', \'\'), \'?\', \'\'), \'\\\'\', \'\'), \'&\', \'\'), \'!\', \'\'), \'.\', \'\'), \' \', \'-\'), \'--\', \'-\'), \'--\', \'-\'))';

GOD SAVE THIS REPLACE!

Credit and thank you @noone0.
Added Azerbaijani characters.
select REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 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(CONCAT(title, '-', id))),':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '/', ''), '"', ''), '?', ''), "'", ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'),'--','-'),'ù','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') ,'%', '') ,'ç', 'c') ,'ü', 'u') ,'ğ', 'g') ,'ş', 's') ,'ı', 'i') ,'.', '') ,'ö', 'ö') ,'ç', 'c') ,'ə', 'e') ,'#x27;', '') ,'&', '') AS slug from table_name

L3az, its a moroccan slang for You are the best you saved my time thank you

Added replacement for '°' character.

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( 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(CONCAT(column1, '-', column2))),':', ''), ')', ''), '(', ''), ',', ''), '\', ''), '/', ''), '"', ''), '?', ''), "'", ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'),'--','-'),'ù','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') ,'%', '') ,'ç', 'c') ,'ü', 'u') ,'ğ', 'g') ,'ş', 's') ,'ı', 'i') ,'.', '') ,'ö', 'ö') ,'ç', 'c') ,'ə', 'e') ,'#x27;', '') ,'&', ''),'°','') AS slug from table_name

thanks 👍

Read comments after made some fixes for my own purposes... but I think it could be relevant to split the replaces as done here since it will avoid having '_' at the beginning or the end of the slugged string

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
		TRIM(		REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
				LOWER(rowToBeSlugged)
			, '·', ''), '|', ''), '®', ''), '`', ''), '°', ''), '$', ''), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\"', ''), '?', ''), '&', ''), '!', ''), '.', ''), '[', ''), ']', '')
		), '’', '-'), '\'', '-'), '–', '-'), ' ', '-'), '--', '-'), '--', '-'), 'ñ', 'n'), 'ç', 'c'), 'ø', 'oe'), 'ó', 'o'), 'ô', 'o'), 'ö', 'o'), 'î', 'i'), 'í', 'i'), 'ï', 'i'), 'ú', 'u'), 'ü', 'u'), 'û', 'u'), 'ù', 'u'), 'ý', 'y'), 'ë', 'e'), 'ê', 'e'), 'é', 'e'), 'è', 'e'), 'å', 'a'), 'â', 'a'), 'ä', 'a'), 'á', 'a'), 'à', 'a'), 'ã', 'a'), '²', '2'), '³', '3'), 'æ', 'ae'), 'œ', 'oe'), 'ḥ', 'h')

Let me know if it's useful for you

Forgot to mention a useful thing:

If you search for rows with slugged issues, you can use

WHERE rowName <> CONVERT(rowName USING ASCII)