Replace spaces with dash and copy into new column

Question

I have table named "city" with column named "city_name" with about 200 records.

I have created another colum named slugs where I want to copy all the records from "city_name" in the same row with spaces replaced with dash - and lowercase.

How can I achieve this via phpmyadmin.

Thanks

Answer 1

You should be able to do this via the following query:

UPDATE city SET slugs=LOWER(REPLACE(city_name, " ", "-"))

Breaking this down, we're using REPLACE to swap all instances of " " with "-" in the existing city_name column, and then passing the result of this to the LOWER function (to convert the data to lower case) before setting the slugs field with this value.

Depending on how "clean" your data is, you might also want to TRIM the data (to remove any leading or trailing spaces in the city_name field) before you apply the REPLACE as such:

UPDATE city SET slugs=LOWER(REPLACE(TRIM(city_name), " ", "-"))

Incidentally, if you've not used (My)SQL much I'd recommend a read of the String Functions manual page - the time you spend on this now will more than repay itself in the future.