Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL to convert a string into a slug
LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM('My String'), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\"', ''), '?', ''), '\'', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-')) AS `post_name`
@NourdineMazali

This comment has been minimized.

Copy link

@NourdineMazali NourdineMazali commented Sep 7, 2015

You saved my life, Thank you infinitely

@richardominq

This comment has been minimized.

Copy link

@richardominq richardominq commented Sep 25, 2015

Thanks, Man XD

@Apatrid

This comment has been minimized.

Copy link

@Apatrid Apatrid commented Oct 8, 2015

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

@xoan-interactive

This comment has been minimized.

Copy link

@xoan-interactive xoan-interactive commented Oct 29, 2015

It helps a lot. Thanks.

@griffithben

This comment has been minimized.

Copy link

@griffithben griffithben commented Dec 14, 2015

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

@ariews

This comment has been minimized.

Copy link

@ariews ariews commented Dec 22, 2015

Thanks!

@ajbrown

This comment has been minimized.

Copy link

@ajbrown ajbrown commented Mar 2, 2016

Saved me hours..thanks!

@Bakharevich

This comment has been minimized.

Copy link

@Bakharevich Bakharevich commented Jan 11, 2017

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), ':', ''), '’', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\"', ''), '?', ''), '\'', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-'))

@CristhianBoujon

This comment has been minimized.

Copy link

@CristhianBoujon CristhianBoujon commented May 8, 2017

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` 
@paveltizek

This comment has been minimized.

Copy link

@paveltizek paveltizek commented Sep 5, 2017

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

@videv

This comment has been minimized.

Copy link

@videv videv commented Oct 16, 2017

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

@noone0

This comment has been minimized.

Copy link

@noone0 noone0 commented Dec 4, 2017

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)

@DanPen

This comment has been minimized.

Copy link

@DanPen DanPen commented Feb 6, 2018

And this is why I've abandoned SQL.

@smknstd

This comment has been minimized.

Copy link

@smknstd smknstd commented Feb 12, 2018

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

@phpawy

This comment has been minimized.

Copy link

@phpawy phpawy commented Jun 13, 2018

really great

@mstaniewski

This comment has been minimized.

Copy link

@mstaniewski mstaniewski commented Sep 30, 2018

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'))
@danielsig

This comment has been minimized.

Copy link

@danielsig danielsig commented Feb 22, 2019

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.

@afaslan

This comment has been minimized.

Copy link

@afaslan afaslan commented Apr 16, 2019

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

@aazwar

This comment has been minimized.

Copy link

@aazwar aazwar commented Nov 16, 2019

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

@danruiyot

This comment has been minimized.

Copy link

@danruiyot danruiyot commented Nov 27, 2019

saved me lots of time

@envatic

This comment has been minimized.

Copy link

@envatic envatic commented Mar 31, 2020

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

@alessandro-aglietti

This comment has been minimized.

Copy link

@alessandro-aglietti alessandro-aglietti commented Apr 24, 2020

GOD SAVE THIS REPLACE!

@teymur-mardaliyev

This comment has been minimized.

Copy link

@teymur-mardaliyev teymur-mardaliyev commented May 30, 2020

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

@Baronsindo

This comment has been minimized.

Copy link

@Baronsindo Baronsindo commented Jun 2, 2020

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

@plazareff

This comment has been minimized.

Copy link

@plazareff plazareff commented Jul 13, 2020

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

@htaoufikallah

This comment has been minimized.

Copy link

@htaoufikallah htaoufikallah commented Dec 13, 2020

thanks 👍

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment