我希望有人能帮助我.我有一列有 160.000 行.很多这些列值(永久链接)以-2"结尾,这是不必要的,所以我想删除它们.但我无法让它工作.
I hope someone can help me. I have a column with 160.000 rows. A lot of those column values (permalinks) end with "-2" which is unneccesary so I want to remove them. But I can't get it to work.
我使用以下查询进行了尝试:
I tried it with the following query:
UPDATE wp_pods_cars SET permalink = Replace(permalink,'-2','') WHERE RIGHT( 'permalink' , 2 ) = '-2';这个查询似乎是有效的,但 RIGHT() 似乎有问题.可能它只能用于 SELECT 而不能用于 WHERE 子句.
This query seems to be valid but the RIGHT() seems to make troubles. Probably it can just be used for a SELECT and not in a WHERE-clause.
我在考虑 Regex,但我也没有让它起作用.我已经无法为我的案例找到正确的正则表达式.
I was thinking about Regex, but I didn't get that to work either. I already fail in finding the right regular expression for my case.
推荐答案您在列名周围有单引号,因此您正在比较 where 子句中的常量字符串.更接近工作的版本是:
You have single quotes around the column name, so you are comparing a constant string in the where clause. The version that comes closer to working is:
UPDATE wp_pods_cars SET permalink = Replace(permalink,'-2','') WHERE RIGHT(permalink, 2 ) = '-2';但是,我会这样写:
UPDATE wp_pods_cars SET permalink = LEFT(permalink, length(permalink) - 2) WHERE permalink LIKE '%-2';-2 可能出现在字符串的其他位置,您不想删除所有出现的地方.
The -2 might appear at other places in the string and you don't want to remove all occurrences.
更多推荐
如果最后 2 个字符匹配,则 SQL 替换最后 2 个字符
发布评论