Question 186477 in Mysql Question posted in
The official MySQL documentation can be found here.

replace Em Dash and En Dash to one Dash MySQL query

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

Tags:

2

Answers


  1. 0 
    mysql> select *, lower(regexp_replace(trim(name), '[^[:alnum:]]+', '-')) as slug from programG
*************************** 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.

    Login or Signup to reply.
  2. 0

    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 |
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search