matoakley/gist:1092571

NourdineMazali · 2015-09-07T16:08:46Z

You saved my life, Thank you infinitely

richardominq · 2015-09-25T23:41:12Z

Thanks, Man XD

Apatrid · 2015-10-08T22:19:35Z

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

xoan-interactive · 2015-10-29T00:57:08Z

It helps a lot. Thanks.

griffithben · 2015-12-14T19:26:36Z

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

ariews · 2015-12-22T12:52:21Z

Thanks!

ajbrown · 2016-03-02T01:57:35Z

Saved me hours..thanks!

Bakharevich · 2017-01-11T17:10:35Z

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 · 2017-05-08T20:22:27Z

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 · 2017-09-05T06:17:25Z

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 · 2017-10-16T14:36:34Z

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 · 2017-12-04T20:15:09Z

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 · 2018-02-06T15:57:03Z

And this is why I've abandoned SQL.

smknstd · 2018-02-12T08:39:21Z

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

phpawy · 2018-06-13T22:26:21Z

really great

mstaniewski · 2018-09-30T00:02:06Z

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 · 2019-02-22T15:36:31Z

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 · 2019-04-16T22:50:58Z

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

aazwar · 2019-11-16T02:41:42Z

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

zyryc · 2019-11-27T09:07:49Z

saved me lots of time

envatic · 2020-03-31T21:33:57Z

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 · 2020-04-24T13:29:12Z

GOD SAVE THIS REPLACE!

teymur-mardaliyev · 2020-05-30T15:35:50Z

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 · 2020-06-02T21:05:30Z

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

plazareff · 2020-07-13T10:10:16Z

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 · 2020-12-13T12:01:39Z

thanks 👍

dominiquevienne · 2021-11-25T10:32:27Z

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

dominiquevienne · 2021-11-25T10:36:23Z

Forgot to mention a useful thing:

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

WHERE rowName <> CONVERT(rowName USING ASCII)

cthiagotavares · 2024-11-08T14:29:08Z

WOW! Thanks!

matoakley/gist:1092571

NourdineMazali commented Sep 7, 2015

Uh oh!

richardominq commented Sep 25, 2015

Uh oh!

Apatrid commented Oct 8, 2015

Uh oh!

xoan-interactive commented Oct 29, 2015

Uh oh!

griffithben commented Dec 14, 2015

Uh oh!

ariews commented Dec 22, 2015

Uh oh!

ajbrown commented Mar 2, 2016

Uh oh!

Bakharevich commented Jan 11, 2017

Uh oh!

CristhianBoujon commented May 8, 2017 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

paveltizek commented Sep 5, 2017 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

videv commented Oct 16, 2017 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

noone0 commented Dec 4, 2017

Uh oh!

DanPen commented Feb 6, 2018

Uh oh!

smknstd commented Feb 12, 2018

Uh oh!

phpawy commented Jun 13, 2018

Uh oh!

mstaniewski commented Sep 30, 2018

Uh oh!

danielsig commented Feb 22, 2019

Uh oh!

afaslan commented Apr 16, 2019

Uh oh!

aazwar commented Nov 16, 2019

Uh oh!

zyryc commented Nov 27, 2019

Uh oh!

envatic commented Mar 31, 2020 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

alessandro-aglietti commented Apr 24, 2020

Uh oh!

teymur-mardaliyev commented May 30, 2020

Uh oh!

Baronsindo commented Jun 2, 2020

Uh oh!

plazareff commented Jul 13, 2020

Uh oh!

htaoufikallah commented Dec 13, 2020

Uh oh!

dominiquevienne commented Nov 25, 2021

Uh oh!

dominiquevienne commented Nov 25, 2021

Uh oh!

cthiagotavares commented Nov 8, 2024

Uh oh!

CristhianBoujon commented May 8, 2017 •

edited

Loading

paveltizek commented Sep 5, 2017 •

edited

Loading

videv commented Oct 16, 2017 •

edited

Loading

envatic commented Mar 31, 2020 •

edited

Loading