replace Em Dash and En Dash to one Dash MySQL query

Question

I found this query on GITHUB https://gist.github.com/matoakley/1092571 that converts strings into slugs, but I'm having problems when string contains EM and EN dashes, need help with this:

SELECT *, LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(name), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\\"', ''), '?', ''), '\'', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-')) as slug FROM program

SQL FIDDLE: http://sqlfiddle.com/#!9/511cc73/1

Result: arrival-–-free-time-or-optional-activities

Need this Result: arrival-free-time-or-optional-activities

@nbk unfortunately yes still on 5.7 – Ered Jun 14 '23 at 19:32 — Ered, Jun 14 '23 at 19:32

score 2 · Accepted Answer · answered Jun 14 '23 at 19:32

Reg_replace makes the query easier, so that you don_#t need for every pattern to add a new replace

Query 1:

-- based on answer https://stackoverflow.com/a/7745635/808921

SELECT *, LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(name), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\\"', ''), '?', ''), '\'', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-'), '-–-', '-')) as slug FROM program

Results:

| id |                                                                name |                                                            slug |
|----|---------------------------------------------------------------------|-----------------------------------------------------------------|
|  1 |                          Arrival – Free time or optional activities |                        arrival-free-time-or-optional-activities |
|  2 | Amazon Adventure: Jondachi canyon hike – Explore underground caves  | amazon-adventure-jondachi-canyon-hike-explore-underground-caves |
|  3 |                                                      Day 1 Explorer |                                                  day-1-explorer |

score 1 · Answer 2 · answered Jun 14 '23 at 19:17

mysql> select *, lower(regexp_replace(trim(name), '[^[:alnum:]]+', '-')) as slug from program\G
*************************** 1. row ***************************
  id: 1
name: Arrival – Free time or optional activities
slug: arrival-free-time-or-optional-activities
*************************** 2. row ***************************
  id: 2
name: Amazon Adventure: Jondachi canyon hike – Explore underground caves 
slug: amazon-adventure-jondachi-canyon-hike-explore-underground-caves
*************************** 3. row ***************************
  id: 3
name: Day 1 Explorer
slug: day-1-explorer

Tested on MySQL 8.0.32. The REGEXP_REPLACE() function is not implemented in earlier versions of MySQL.

any chance to do this without regexp_replace? using old mysql 5.7 — Ered, Jun 14 '23 at 19:24
The other answer provided a solution. I'll leave this here for when you or other readers have upgraded to MySQL 8.0. — Bill Karwin, Jun 14 '23 at 21:04

replace Em Dash and En Dash to one Dash MySQL query

2 Answers2