Avoiding errors concatenating fields with numerical values in SQL Server

By Carlos on Wed, 09/25/2013 - 22:51
Databases

The operator to concatenate in SQL Server is '+', but this operator is also used to add values.

Being the same operator for both, the query analyzer makes a concatenation or a sum based on the type of data fields being treated. If the fields are of 'string' type, the operator concatenate, and if it has numeric fields, two integers, for example, it add the two values.

The problem arises when a field of each type and the '+' operator is in the middle. In this case, the engine may return a conversion error because upon finding a numeric field, it tries to add and, of course, the other field is a string.

concatenate strings

For example, we have these fields in a table EmployeesTable: 
 strName -> nvarchar (10) 
 intAge -> integer 

If we do the following SELECT with SQL Server:

SELECT strName + ' has ' + intAge + ' age'  
FROM EmployeesTable

We find this error message:


Mess. 245, Level 16, Status 1, Line 1
Conversion error converting the value nvarchar 'age' 
to data type int.

 

The solution is quite simple, if what you want is to concatenate, you must convert into string any numeric field included in the concatenation: 

SELECT strName + ' has ' + cast(intAge as varchar) + ' age'  
FROM EmployeesTablle

 

It should be noted that this problem is specific to SQL Server, as the SQL standard operator for concatenation is '||', which is used by other databases such as Oracle. MySQL, although it does not use the standard, concatenates with the CONCAT() function.

In this way, in both databases (Oracle and MySQL), the '+' operator is used only for adding, so when concatenating it will never happen that the parser interprets that an arithmetic operation is desired.