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

Question

SELECT notes + 'SomeText'
FROM NotesTable a

gives The data types nvarchar and text are incompatible in the add operator. as an error.

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

If you are using SQL server 2005 (or greater) you might want to consider switching to nvarchar(max) in your table definition, because TEXT, NTEXT and IMAGE data types of SQL Server 2000 will be deprecated in future version of SQL Server, SQL Server 2005 provides backward compatibility to data types but it is recommanded to use new data types which are VARHCAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX).

Answer 5

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

Answer 6

Hi i wish to see some litral example in sql server like oracle