MySQL automatically change columns

Question

I'm really bad in MySQL, and I really don't know is it even possible. But I have 6k records and don't want to do it manually.

I have to columns: Name and Slug. Names can be different, for example Dancer, Hip Hop, Rock (hard).

And what I want to do is for each record change Slug automatically depending on Name, with converting to slug. For example, for Dancer do dancer, for Hip Hop => hip-hop, for Rock (hard) => rock-hard.

Is it even possible?

https://gist.github.com/matoakley/1092571 – e4c5 Jan 04 '17 at 00:55 — e4c5, Jan 04 '17 at 00:55

Asaph · Accepted Answer · 2017-01-04T00:58:47.750

1

You should be able to update all your slugs in 1 query using LCASE() and REPLACE(). Something like this should work:

update mytable set Slug=lcase(replace(Name, ' ', '-'));

edited Jan 04 '17 at 00:58

answered Jan 04 '17 at 00:57

Asaph

159,146
25
197
199

1

What about `Rock (hard)` => `rock-hard`? – dana Jan 04 '17 at 00:58
The second word may also begin with an uppercase letter, but your solution would lowercase that too. – Tim Biegeleisen Jan 04 '17 at 00:59
You can nest more replace calls to replace `(` and `)` with empty strings. – Asaph Jan 04 '17 at 01:00
Thnx you for answer. `lcase` - it's for lower case. but what do `replace(Name, ' ', '-'));` – Andrey Drozdov Jan 04 '17 at 01:00
That replaces a space with a dash – Asaph Jan 04 '17 at 01:00
@Asaph and how to insert additional rules? (for example not only spaces but dots too and delete `(`) – Andrey Drozdov Jan 04 '17 at 01:02
@AndreyDrozdov You can nest another call to `REPLACE()` for each character substitution. Your query may get long and tedious but it will work. – Asaph Jan 04 '17 at 01:04

MySQL automatically change columns

1 Answers1