How do I concatenate text in a query in sql server?
SqlSql ServerSql Problem Overview
The following SQL:
SELECT notes + 'SomeText'
FROM NotesTable a
Give the error:
> The data types nvarchar and text are incompatible in the add operator.
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
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
Solution 3 - Sql
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
Solution 4 - Sql
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)).
Solution 5 - Sql
Another option is the [CONCAT][1] command:
SELECT CONCAT(MyTable.TextColumn, 'Text') FROM MyTable
[1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql "CONCAT"
Solution 6 - Sql
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.