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

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和文本在add操作符中不兼容。

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

您可能会在MySQL上使用concat，但是如何在SQL服务器上使用concat呢?

6 个解决方案

#1

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)，但如果不可能，您可以使用cast或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

hope that helps

希望这有助于

#2

Stop using the TEXT data type in SQL Server!

停止在SQL Server中使用文本数据类型!

It's been deprecated since the 2005 version. Use VARCHAR(MAX) instead, if you need more than 8000 characters.

自从2005年版本以来，它就被弃用了。如果需要超过8000个字符，可以使用VARCHAR(MAX)。

The TEXT data 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.

文本数据类型不支持普通的字符串函数，而VARCHAR(MAX)则支持—如果只使用VARCHAR类型，那么您的语句就可以正常工作。

#3

The + (String Concatenation) does not work on SQL Server for the image, ntext, or text data types.

对于图像、ntext或文本数据类型，+(字符串连接)并不适用于SQL Server。

In fact, image, ntext, and text are all deprecated.

事实上，图像、ntext和文本都不赞成。

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.

ntext、文本和图像数据类型将在microsoftsqlserver的未来版本中删除。避免在新的开发工作中使用这些数据类型，并计划修改当前使用它们的应用程序。使用nvarchar(max)、varchar(max)和varbinary(max)代替。

That said if you are using an older version of SQL Server than you want to use UPDATETEXT to 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在他的博客文章中给出了一个很好的例子，在一个文本列(SQL 2000 vs SQL 2005+)上连接字符串。

#4

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.

我也建议不要使用文本数据类型，而是使用varchar。

If that doesn't work, try ' ' + CAST ([myText] AS VARCHAR(255))

如果不行，可以尝试' ' ' + CAST ([myText])作为VARCHAR(255)

#5

UPDATE test SET a = CONCAT(a, "more text")

#6

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

#1