Joining fields in SQL – Concatenating

Joining fields in SQL – Concatenating

Another common requirement in SQL, is to join two or more fields into a single output field.  This is called concatenating fields.

To concatenate fields in SQL, the plus (+) sign is used.  The first field is specified, then the plus sign, then the second field, and so on.  A simple concatenation of FIRST_NAME and LAST_NAME in the table TABLEA, separated by a space, would be done as:

     SELECT FIRST_NAME+”+LAST_NAME FROM TABLEA

Which is very straight forward.  

However, issues can arise if any of the fields are numeric.  This is because SQL, seeing a numeric field, will try to perform an arithmetical add, instead of a concatenate. If, for example, TABLEA above, also has an integer field named CUST_ID.  For this example, assume that a row in TABLEA contains:

     CUST_ID=1
     FIRST_NAME=’John’
     LAST_NAME=’DOE’

If you want to output the CUST_ID, followed by a dash (-), followed by FIRST_NAME and LAST_NAME, the logical statement would be:

     SELECT CUST_ID+’-‘+FIRST_NAME+”+LAST_NAME FROM TABLEA

However, this will give the following error:

     Conversion failed when converting the varchar value ‘John’ to data type int

In other words, SQL is trying to convert the FIRST_NAME (John) to an integer, and then add the CUST_ID (1) to it.

In order to have SQL treat the CUST_ID field as a non-numeric field, typically a VARCHAR, the CAST function needs to be used.  The simplest for of this, would be:

     SELECT cast(CUST_ID as varchar)+’-‘+FIRST_NAME+”+LAST_NAME FROM TABLEA

This first tells SQL to treat the CUST_ID as a VARCHAR, instead of its native INT, then concatenate the fields.  The resulting output is:

     1-John Doe

That is the only issue that I have run into when concatenating fields.

Enjoy Dave!

 

Leave a Reply