How do I concatenate text in a query in sql server

Question

The following SQL   SELECT notes    SomeText  FROM NotesTable a    Give the error      The data types nvarchar and text are incompatible in the add operator

User · Answer

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

User · Answer

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

User · Answer

Another option is the CONCAT command   SELECT CONCAT MyTable TextColumn   Text   FROM MyTable

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server