matoakley/gist:1092571

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

xoan-interactive commented Oct 29, 2015

griffithben commented Dec 14, 2015

ariews commented Dec 22, 2015

ajbrown commented Mar 2, 2016

Bakharevich commented Jan 11, 2017

CristhianBoujon commented May 8, 2017 •

edited

Loading

paveltizek commented Sep 5, 2017 •

edited

Loading

videv commented Oct 16, 2017 •

edited

Loading

noone0 commented Dec 4, 2017

DanPen commented Feb 6, 2018

smknstd commented Feb 12, 2018

phpawy commented Jun 13, 2018

mstaniewski commented Sep 30, 2018

danielsig commented Feb 22, 2019

afaslan commented Apr 16, 2019

aazwar commented Nov 16, 2019

zyryc commented Nov 27, 2019

envatic commented Mar 31, 2020 •

edited

Loading

alessandro-aglietti commented Apr 24, 2020

teymur-mardaliyev commented May 30, 2020

Baronsindo commented Jun 2, 2020

plazareff commented Jul 13, 2020

htaoufikallah commented Dec 13, 2020

dominiquevienne commented Nov 25, 2021

dominiquevienne commented Nov 25, 2021

cthiagotavares commented Nov 8, 2024

matoakley/gist:1092571

xoan-interactive commented Oct 29, 2015

griffithben commented Dec 14, 2015

ariews commented Dec 22, 2015

ajbrown commented Mar 2, 2016

Bakharevich commented Jan 11, 2017

CristhianBoujon commented May 8, 2017 • edited Loading

paveltizek commented Sep 5, 2017 • edited Loading

videv commented Oct 16, 2017 • edited Loading

noone0 commented Dec 4, 2017

DanPen commented Feb 6, 2018

smknstd commented Feb 12, 2018

phpawy commented Jun 13, 2018

mstaniewski commented Sep 30, 2018

danielsig commented Feb 22, 2019

afaslan commented Apr 16, 2019

aazwar commented Nov 16, 2019

zyryc commented Nov 27, 2019

envatic commented Mar 31, 2020 • edited Loading

alessandro-aglietti commented Apr 24, 2020

teymur-mardaliyev commented May 30, 2020

Baronsindo commented Jun 2, 2020

plazareff commented Jul 13, 2020

htaoufikallah commented Dec 13, 2020

dominiquevienne commented Nov 25, 2021

dominiquevienne commented Nov 25, 2021

cthiagotavares commented Nov 8, 2024

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