SQL Server - 将字符串添加到文本列(concat 等效项)

问题描述

如何向 SQL Server 中的列添加字符串?

How do you add a string to a column in SQL Server?

UPDATE [myTable] SET [myText]=' '+[myText]

这不起作用:

数据类型 varchar 和 text 在加法运算符中不兼容.

The data types varchar and text are incompatible in the add operator.

你会在 MySQL 上使用 concat，但你如何在 SQL Server 上使用它?

You would use concat on MySQL, but how do you do it on SQL Server?

推荐答案

就像之前所说的，最好将列的数据类型设置为 nvarchar(max)，但如果这不可能，您可以使用 cast 或 convert 执行以下操作:

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:

-- 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

希望有帮助