I have table named "city" with column named "city_name" with about 200 records.
我有一个名为“city”的表，列名为“city_name”，大约有200条记录。
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.
我创建了另一个名为slugs的列，我想在同一行中复制“city_name”中的所有记录，并用空格 - 小写替换空格。
How can I achieve this via phpmyadmin.
我怎样才能通过phpmyadmin实现这一目标。
Thanks
谢谢
2 个解决方案
#1
11
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.
打破这一点，我们使用REPLACE在现有的city_name列中交换“”与“ - ”的所有实例，然后在设置slugs之前将结果传递给LOWER函数（将数据转换为小写）具有此值的字段。
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:
根据数据的“清理”方式，您可能还需要在应用REPLACE之前TRIM数据（删除city_name字段中的任何前导或尾随空格）：
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.
顺便说一句，如果您没有使用（我的）SQL，我建议您阅读字符串函数手册页 - 您现在花在此上的时间将来不仅仅会回报。
#2
0
Here is SQLFiddle
这是SQLFiddle
MYSql documentation for function LOWER(str) and REPLACE(str,from_str,to_str)
函数LOWER（str）和REPLACE（str，from_str，to_str）的MYSql文档
UPDATE city SET slugs = LOWER(REPLACE(city_name," ", "-"));
#1
11
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.
打破这一点，我们使用REPLACE在现有的city_name列中交换“”与“ - ”的所有实例，然后在设置slugs之前将结果传递给LOWER函数（将数据转换为小写）具有此值的字段。
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:
根据数据的“清理”方式，您可能还需要在应用REPLACE之前TRIM数据（删除city_name字段中的任何前导或尾随空格）：
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.
顺便说一句，如果您没有使用（我的）SQL，我建议您阅读字符串函数手册页 - 您现在花在此上的时间将来不仅仅会回报。
#2
0
Here is SQLFiddle
这是SQLFiddle
MYSql documentation for function LOWER(str) and REPLACE(str,from_str,to_str)
函数LOWER（str）和REPLACE（str，from_str，to_str）的MYSql文档
UPDATE city SET slugs = LOWER(REPLACE(city_name," ", "-"));