如何在 sql server 中的查询中连接文本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/54334/
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 do I concatenate text in a query in sql server?
提问by Greg Ogle
The following SQL:
以下 SQL：
SELECT notes + 'SomeText'
FROM NotesTable a
Give the error:
给出错误：
The data types nvarchar and text are incompatible in the add operator.
数据类型 nvarchar 和 text 在 add 运算符中不兼容。
回答by GateKiller
The only way would be to convert your text field into an nvarchar field.
唯一的方法是将您的文本字段转换为 nvarchar 字段。
Select Cast(notes as nvarchar(4000)) + 'SomeText'
From NotesTable a
Otherwise, I suggest doing the concatenation in your application.
否则，我建议在您的应用程序中进行连接。
回答by Chris Wuestefeld
You might want to consider NULL values as well. In your example, if the column noteshas a null value, then the resulting value will be NULL. If you want the null values to behave as empty strings (so that the answer comes out 'SomeText'), then use the IsNull function:
您可能还想考虑 NULL 值。在您的示例中，如果列注释具有空值，则结果值将为 NULL。如果您希望空值表现为空字符串（以便答案为“SomeText”），则使用 IsNull 函数：
Select IsNull(Cast(notes as nvarchar(4000)),'') + 'SomeText' From NotesTable a
回答by Scott Nichols
If you are using SQL Server 2005 or greater, depending on the size of the data in the Notes field, you may want to consider casting to nvarchar(max) instead of casting to a specific length which could result in string truncation.
如果您使用的是 SQL Server 2005 或更高版本，根据 Notes 字段中数据的大小，您可能需要考虑转换为 nvarchar(max) 而不是转换为可能导致字符串截断的特定长度。
Select Cast(notes as nvarchar(max)) + 'SomeText' From NotesTable a
回答by Craig
You have to explicitly cast the string types to the same in order to concatenate them, In your case you may solve the issue by simply addig an 'N' in front of 'SomeText' (N'SomeText'). If that doesn't work, try Cast('SomeText' as nvarchar(8)).
您必须将字符串类型显式转换为相同类型才能连接它们，在您的情况下，您可以通过在“SomeText”（N'SomeText'）前面添加一个 'N' 来解决问题。如果这不起作用，请尝试 Cast('SomeText' as nvarchar(8))。
回答by David Gausmann
回答by edosoft
If you are using SQL Server 2005 (or greater) you might want to consider switching to NVARCHAR(MAX) in your table definition; TEXT, NTEXT, and IMAGE data types of SQL Server 2000 will be deprecated in future versions of SQL Server. SQL Server 2005 provides backward compatibility to data types, but you should probably be using VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) instead.
如果您使用的是 SQL Server 2005（或更高版本），您可能需要考虑在表定义中切换到 NVARCHAR(MAX)；SQL Server 2000 的 TEXT、NTEXT 和 IMAGE 数据类型将在 SQL Server 的未来版本中弃用。SQL Server 2005 提供对数据类型的向后兼容性，但您可能应该改用 VARCHAR(MAX)、NVARCHAR(MAX) 和 VARBINARY(MAX)。
