Replace the dash spaces and copy to a new column

Question

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

I created another colum called slugs, where I want to copy all the entries from "city_name" on the same line where the spaces are replaced by dashes and lowercase letters.

How can I achieve this through phpmyadmin.

thanks

Answer 1

You must do this by running the following query:

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

In this case, we use REPLACE to replace all instances with "-" in the existing city_name column, and then pass 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 you “clear” your data, you can also TRIM data (to remove all leading or trailing spaces in the city_name field) before applying REPLACE as such:

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

By the way, if you did not use (My) SQL, I would recommend reading the String Functions manual page - the time you spend on it now will be more than paying off in the future.

Answer 2

Here is the SQLFiddle

MYSql documentation for the functions LOWER (str) and REPLACE (str, from_str, to_str)

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