SQL Server - 将字符串添加到文本列（concat 等效项）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3099237/
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
SQL Server - Adding a string to a text column (concat equivalent)
提问by Aximili
How do you add a string to a column in SQL Server?
如何将字符串添加到 SQL Server 中的列中？
UPDATE [myTable] SET [myText]=' '+[myText]
That doesn't work:
这不起作用：
The data types varchar and text are incompatible in the add operator.
数据类型 varchar 和 text 在 add 运算符中不兼容。
You would use concat on MySQL, but how do you do it on SQL Server?
你会在 MySQL 上使用 concat，但你如何在 SQL Server 上使用它呢？
回答by Tobias Pirzer
like said before best would be to set datatype of the column to nvarchar(max), but if that's not possible you can do the following using cast or convert:
如前所述，最好将列的数据类型设置为 nvarchar(max)，但如果这是不可能的，您可以使用强制转换或转换执行以下操作：
-- create a test table
create table test (
a text
)
-- insert test value
insert into test (a) values ('this is a text')
-- the following does not work !!!
update test set a = a + ' and a new text added'
-- but this way it works:
update test set a = cast ( a as nvarchar(max)) + cast (' and a new text added' as nvarchar(max) )
-- test result
select * from test
-- column a contains:
this is a text and a new text added
hope that helps
希望有帮助
回答by marc_s
Stop using the TEXTdata type in SQL Server!
停止使用TEXTSQL Server 中的数据类型！
It's been deprecated since the 2005 version. Use VARCHAR(MAX)instead, if you need more than 8000 characters.
自 2005 版本以来，它已被弃用。VARCHAR(MAX)如果您需要超过 8000 个字符，请改用。
The TEXTdata type doesn't support the normal string functions, while VARCHAR(MAX)does - your statement would work just fine, if you'd be using just VARCHAR types.
该TEXT数据类型不支持正常的字符串函数，而VARCHAR(MAX)做-你的声明会工作得很好，如果你可以只使用VARCHAR类型。
回答by ahsteele
The + (String Concatenation)does not work on SQL Server for the image, ntext, or text data types.
该+（字符串串联）不起作用SQL Server上的图像，ntext或文本数据类型。
In fact, image, ntext, and text are all deprecated.
实际上，image、ntext 和 text 都已弃用。
ntext, text, and image data types will be removed in a future version of MicrosoftSQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
MicrosoftSQL Server 的未来版本中将删除 ntext、text 和 image 数据类型。避免在新的开发工作中使用这些数据类型，并计划修改当前使用它们的应用程序。请改用 nvarchar(max)、varchar(max) 和 varbinary(max)。
That said if you are using an older version of SQL Server than you want to use UPDATETEXTto perform your concatenation. Which Colin Stasiuk gives a good example of in his blog post String Concatenation on a text column (SQL 2000 vs SQL 2005+).
也就是说，如果您使用的是旧版本的 SQL Server 而不是您想使用UPDATETEXT来执行您的连接。Colin Stasiuk 在他的博客文章String Concatenation on a text column (SQL 2000 vs SQL 2005+) 中给出了一个很好的例子。
回答by koesys
UPDATE test SET a = CONCAT(a, "more text")
回答by Meiscooldude
hmm, try doing CAST(' ' AS TEXT) + [myText]
嗯，试着做 CAST(' ' AS TEXT) + [myText]
Although, i am not completely sure how this will pan out.
虽然，我不完全确定这将如何发展。
I also suggest against using the Text datatype, use varchar instead.
我还建议不要使用 Text 数据类型，而是使用 varchar。
If that doesn't work, try ' ' + CAST ([myText] AS VARCHAR(255))
如果这不起作用，请尝试 ' ' + CAST ([myText] AS VARCHAR(255))
回答by Muhammad Aamir Jahangir
To Join two string in SQL Query use function CONCAT(Express1,Express2,...)
要在 SQL 查询中加入两个字符串，请使用函数 CONCAT(Express1,Express2,...)
Like....
喜欢....
SELECT CODE, CONCAT(Rtrim(FName), " " , TRrim(LName)) as Title FROM MyTable
