How to link text in a query on sql server?

Question

Next SQL:

SELECT notes + 'SomeText' FROM NotesTable a 

Give an error:

The nvarchar data types and text are not compatible in the add statement.

Answer 1

The only way is to convert the text field to nvarchar field.

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

Otherwise, I suggest concatenating in your application.

Answer 2

You might also want to consider NULL values. In your example, if the column notes is null, then the resulting value will be NULL. If you want the null values ​​to behave like empty strings (so that the answer comes out "SomeText"), use the IsNull function:

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

Answer 3

If you are using SQL Server 2005 or later, depending on the size of the data in the Notes field, you may need casting for nvarchar (max) instead of casting to a specific length, which may result in truncation of the string.

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

Answer 4

You must explicitly use string types to concatenate them. In this case, you can solve the problem by simply adding "N" before "SomeText" (N'SomeText '). If this does not work, try Cast ('SomeText' as nvarchar (8)).

Answer 5

If you are using SQL Server 2005 (or higher), you may need to switch to NVARCHAR (MAX) in your table definition; TEXT, NTEXT, and IMAGE SQL Server 2000 will be deprecated in future versions of SQL Server. SQL Server 2005 provides backward compatibility with data types, but you should probably use VARCHAR (MAX), NVARCHAR (MAX), and VARBINARY (MAX) instead.