如果最后 2 个字符匹配，则 SQL 替换最后 2 个字符

本文介绍了如果最后 2 个字符匹配，则 SQL 替换最后 2 个字符的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我希望有人能帮助我.我有一列有 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.