How do I concatenate text in a query in sql server?

The following SQL:

SELECT notes + 'SomeText'
FROM NotesTable a

Give the error:

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

291126 次浏览

小开

最佳答案

The only way would be to convert your text field into an nvarchar field.

Select Cast(notes as nvarchar(4000)) + 'SomeText'
From NotesTable a

Otherwise, I suggest doing the concatenation in your application.

小开

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

小开

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.

Select Cast(notes as nvarchar(max)) + 'SomeText' From NotesTable a

小开

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.

小开

You might want to consider NULL values as well. In your example, if the column notes has 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:

Select IsNull(Cast(notes as nvarchar(4000)),'') + 'SomeText' From NotesTable a

小开

Another option is the CONCAT command:

SELECT CONCAT(MyTable.TextColumn, 'Text') FROM MyTable