如何在 SQL Server 中给定列的末尾追加字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/649003/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Append String at the end of a Given a Column in SQL Server?
提问by Ash
I have a project I am working on. based off a backup SQl Server database from a production server. They have over 16,000 user email addresses, and I want to corrupt them so the system (which has automatic emailers) will not send any emails to valid addresses.
我有一个正在做的项目。基于来自生产服务器的备份 SQL Server 数据库。他们有超过 16,000 个用户电子邮件地址，我想破坏它们，以便系统（具有自动电子邮件发送器）不会向有效地址发送任何电子邮件。
But I still want the users, and I want them in a way that I can reverse what I do (which is why I dont want to delete them).
但是我仍然想要用户，并且我想要他们以一种可以逆转我所做的事情的方式（这就是我不想删除它们的原因）。
The SQL I am trying is:
我正在尝试的 SQL 是：
UPDATE Contact SET
EmailAddress = EmailAddress + '.x'
更新联系人 SET
EmailAddress = EmailAddress + '.x'
But it isnt working, what am I doing wrong?
但它不起作用，我做错了什么？
Error Message is as follows:
错误信息如下：
---------------------------
Microsoft SQL Server Management Studio Express
---------------------------
SQL Execution Error.
Executed SQL statement: UPDATE Contact SET EmailAddress = EmailAddress + '.x'
Error Source: .Net SqlClient Data Provider
Error Message: String or binary data would be truncated. The statement has been terminated.
---------------------------
OK Help
---------------------------
采纳答案by JoshBerke
The issue is that EmailAddress +".x"results in some of your data being to long for the given field. You could do:
问题是EmailAddress +".x"导致您的某些数据对于给定字段太长了。你可以这样做：
select * from Contact where len(EmailAddress +".x") > LENFIELD
Replace LENFIELD with the length of the column defined on the table. If you just want to mung the data why not just set all the fields to a single email address? Or modify the rows that are causing the error to occur to be shorter.
将 LENFIELD 替换为表中定义的列的长度。如果您只想处理数据，为什么不将所有字段设置为单个电子邮件地址？或者修改导致错误发生的行更短。
回答by MrTelly
Can you be more specific about any errors that you get? I've just knocked up an example and it works fine.
你能更具体地说明你遇到的任何错误吗？我刚刚敲了一个例子，它工作正常。
Edit - EmailAddress fields you're trying to update are already close to the full size for the field, to make sure the edit applies to all the required record, you need to change add 2 to the column size for that field
编辑 - 您尝试更新的 EmailAddress 字段已经接近该字段的完整大小，为了确保编辑适用于所有必需的记录，您需要更改该字段的列大小添加 2
BTW Sql to convert it back again
BTW Sql 再次将其转换回来
update Contact
set EmailAddress = SUBSTRING(EmailAddress , 0 , len(EmailAddress ) - 1)
where SUBSTRING(EmailAddress , len(EmailAddress ) - 1, 2) = '.x'
回答by Dan Breslau
Are these fully-qualified email addresses, with @domain.name? In that case, you could use UPDATE... SELECT REPLACE to change the @ to, say, *.
这些完全合格的电子邮件地址是否带有@domain.name? 在这种情况下，您可以使用 UPDATE... SELECT REPLACE 将 @ 更改为 *。
回答by Aidan Ryan
It looks to me like appending the extra text will make one or more of the email addresses longer than the field size. Rather than appending why don't you replace the last character with a different one?
在我看来，附加额外的文本会使一个或多个电子邮件地址长于字段大小。而不是附加为什么不用不同的字符替换最后一个字符？
回答by vmarquez
First result on Google searching for the error message says:
谷歌搜索错误消息的第一个结果是：
"String or binary data would be truncated" MS Sql error
“字符串或二进制数据将被截断” MS Sql 错误
This problem occurs when you trying to insert to field a string that exceeds fields length. The only solution I could find was to set a bigger field length.
当您尝试向字段插入超过字段长度的字符串时，会出现此问题。我能找到的唯一解决方案是设置更大的字段长度。
Ref: http://www.dotnetjunkies.com/WebLog/skiff/archive/2005/01/31/49336.aspx
参考：http: //www.dotnetjunkies.com/WebLog/skiff/archive/2005/01/31/49336.aspx
回答by Martlark
try:
尝试：
UPDATE Contact SET EmailAddress = EmailAddress || '.x';
更新联系人 SET EmailAddress = EmailAddress || '。X';
the || is the string (varchar) concatanation operator in SQL.
|| 是 SQL 中的字符串 (varchar) 串联运算符。
HINT: Error messages would help if asking more questions.
提示：如果提出更多问题，错误消息会有所帮助。
