如果最后 2 个字符匹配，则 SQL 替换最后 2 个字符 [英] SQL Replace last 2 chars if last 2 chars matches

问题描述

我希望有人能帮助我.我有一列有 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 个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！