使用 T-SQL/MS-SQL 将字符串附加到现有表格单元格的最简单方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17624/
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
What is the easiest way using T-SQL / MS-SQL to append a string to existing table cells?
提问by Matt Mitchell
I have a table with a 'filename' column. I recently performed an insert into this column but in my haste forgot to append the file extension to all the filenames entered. Fortunately they are all '.jpg' images.
我有一个带有“文件名”列的表。我最近在此列中执行了插入操作，但我匆忙忘记将文件扩展名附加到所有输入的文件名中。幸运的是，它们都是“.jpg”图像。
How can I easily update the 'filename' column of these inserted fields (assuming I can select the recent rows based on known id values) to include the '.jpg' extension?
如何轻松更新这些插入字段的“文件名”列（假设我可以根据已知 id 值选择最近的行）以包含“.jpg”扩展名？
回答by Matt Mitchell
The solution is:
解决办法是：
UPDATE tablename SET [filename] = RTRIM([filename]) + '.jpg' WHERE id > 50
RTRIM is required because otherwise the [filename] column in its entirety will be selected for the string concatenation i.e. if it is a varchar(20) column and filename is only 10 letters long then it will still select those 10 letters and then 10 spaces. This will in turn result in an error as you try to fit 20 + 3 characters into a 20 character long field.
RTRIM 是必需的，因为否则整个 [filename] 列将被选择用于字符串连接，即如果它是一个 varchar(20) 列并且文件名只有 10 个字母长，那么它仍然会选择这 10 个字母和 10 个空格。当您尝试将 20 + 3 个字符放入 20 个字符长的字段时，这将反过来导致错误。
回答by samjudson
MattMitchell's answer is correct if the column is a CHAR(20), but is not true if it was a VARCHAR(20) and the spaces hadn't been explicitly entered.
如果该列是 CHAR(20)，则 MattMitchell 的答案是正确的，但如果它是 VARCHAR(20) 并且未明确输入空格，则答案不正确。
If you do try it on a CHAR field without the RTRIM function you will get a "String or binary data would be truncated"error.
如果您在没有 RTRIM 函数的 CHAR 字段上尝试它，您将收到“字符串或二进制数据将被截断”错误。
回答by Matt Hamilton
Nice easy one I think.
我认为很简单的一个。
update MyTable
set filename = filename + '.jpg'
where ...
Edit: Ooh +1 to @MattMitchell's answer for the rtrim suggestion.
编辑：Ooh +1 @MattMitchell 对 rtrim 建议的回答。
回答by Amy B
If the original data came from a char column or variable (before being inserted into this table), then the original data had the spaces appended before becoming a varchar.
如果原始数据来自 char 列或变量（在插入此表之前），则原始数据在成为 varchar 之前附加了空格。
DECLARE @Name char(10), @Name2 varchar(10)
SELECT
@Name = 'Bob',
@Name2 = 'Bob'
SELECT
CASE WHEN @Name2 = @Name THEN 1 ELSE 0 END as Equal,
CASE WHEN @Name2 like @Name THEN 1 ELSE 0 END as Similiar
Life Lesson : never use char.
人生教训：永远不要使用字符。
回答by Ricardo C
The answer to the mystery of the trailing spaces can be found in the ANSI_PADDING
尾随空格之谜的答案可以在ANSI_PADDING中找到
For more information visit: SET ANSI_PADDING (Transact-SQL)
有关更多信息，请访问：SET ANSI_PADDING (Transact-SQL)
The default is ANSI_PADDIN ON. This will affect the column only when it is created but not to existing columns.
默认值为 ANSI_PADDIN ON。这只会在创建时影响列，但不会影响现有列。
Before you run the update query, verify your data. It could have been compromised.
在运行更新查询之前，请验证您的数据。它可能已经受到损害。
Run the following query to find compromised rows:
运行以下查询以查找受损行：
SELECT *
FROM tablename
WHERE LEN(RTRIM([filename])) > 46
-- The column size varchar(50) minus 4 chars
-- for the needed file extension '.jpg' is 46.
These rows either have lost some characters or there is not enough space for adding the file extension.
这些行要么丢失了一些字符，要么没有足够的空间来添加文件扩展名。
回答by Dr8k
I wanted to adjust David B's "Life Lesson". I think it should be "never use char for variable length string values" -> There are valid uses for the char data type, just not as many as some people think :)
我想调整大卫 B 的“生活课”。我认为它应该是“永远不要将 char 用于可变长度的字符串值”-> char 数据类型有有效的用途，只是不像某些人想象的那么多:)
