英文:
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
英文:
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
答案1
得分: 2
Reg_replace使查询更容易，这样你就不需要为每个模式添加一个新的替换。
英文:
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
| 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 |
答案2
得分: 1
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.
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论