Nvarchar列值增加了一倍，我如何检测和修复它?

I've inherited a project that somehow ended up with random rows in the application settings table getting duplicated. I got the duplicate rows removed successfully, but then I noticed that the actual values, of type nvarchar, for said rows are also duplicated.

我继承了一个项目，该项目最终导致应用程序设置表中的随机行被复制。我成功地删除了重复的行，但是我注意到nvarchar类型的实际值也被复制了。

For instance, one row has Key column Error Email Address and Value column websupport@mycompany.com,websupport@mycompany.com. The Value column should just contain websupport@mycompany.com. There are numerous records like this, all following the same pattern of The value,The value.

例如，有一行包含键列错误电子邮件地址和值列websupport@mycompany.com、websupport@mycompany.com。Value列应该只包含websupport@companymy.com。有许多这样的记录，它们都遵循相同的值模式。

How can I detect when the Value column contains this kind of duplicated data and correct it?

当Value列包含此类重复数据时，如何检测并纠正它?

Note that the comma alone is not enough to say the row is invalid, because things like Key Default Error Message Value Oops, something went wrong are correct and also contain a comma.

注意，仅用逗号还不足以说明行是无效的，因为像Key Default Error Message值之类的东西出错了，而且还包含一个逗号。

4 个解决方案

#1

Here is the query:

在这里查询:

Update TableName
set Col1=substring(Col1,0,charindex(',',Col1))
where substring(Col1,0,charindex(',',Col1))=substring(Col1,charindex(',',Col1)+1,500)

Replace Col1 with the column name, and TableName with the actual table name. It will only replace the rows where value before and after comma(,) are same with a single value.

用列名替换Col1，用实际的表名替换TableName。它将只替换逗号(，)前后值与单个值相同的行。

#2

Here is an update that would solve the problem:

下面是一个可以解决这个问题的更新:

update t
    set value = left(value, len(val)/2)
    where left(value, len(val)/2) = right(value, len(val)/2) and
          substring(value, (len(val)/2) + 1, 1) = ',';

You can validate the logic by doing select first:

您可以通过执行select first来验证逻辑:

select value
from t
where left(value, len(val)/2) = right(value, len(val)/2) and
      substring(value, (len(val)/2) + 1, 1) = ',';

#3

select * from blah where email like '%@%,%@%'

从“%@%，%@%”之类的电子邮件中选择*

It does seem like you have other issues to deal with regarding how the bad data is being inserted.

对于如何插入坏数据，您似乎还有其他问题要处理。

#4

I would try something like https://*.com/a/5123680/1504882 Where you would split the column on the column and when Left = Right update the column to (whatever you choose, left/right)

我可以试试https://*.com/a/5123680/1504882这样你就可以把列分成两半当左=右时将列更新为(无论你选择什么，左/右)

#1