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:
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:
That is the only issue that I have run into when concatenating fields.