Concatenate

There are many instances where we will want to join multiple fields and/or strings to form new fields. This can be accomplished using the CONCAT function in SQL.

We will be using the classicmodels database, which you can download from mysqltutorial for this article.

The Customers Table

SELECT
   contactFirstName,
   contactLastName 
FROM
   customers 
WHERE
   customerNumber IN 
   (
      103,
      112,
      114,
      119,
      121
   )
;

The customers table holds information about each customer. For this example, we aim to create a new variable, contactFullName, which joins contactFirstName and contactLastName.

SELECT
   contactFirstName,
   contactLastName,
   CONCAT(TRIM(contactFirstName), ' ', TRIM(contactLastName)) contactFullName 
FROM
   customers 
WHERE
   customerNumber IN 
   (
      103,
      112,
      114,
      119,
      121
   )
;

The CONCAT function takes multiple variables and returns a joined version of it. Note that we used the TRIM function too, as there were trailing spaces in some of the contactFirstName values.