Recipe1.7.Concatenating Column Values

Recipe 1.7. Concatenating Column Values

Problem

You want to return values in multiple columns as one column. For example, you would like to produce this result set from a query against the EMP table:

 CLARK WORKS AS A MANAGER KING WORKS AS A PRESIDENT MILLER WORKS AS A CLERK 

However, the data that you need to generate this result set comes from two different columns, the ENAME and JOB columns in the EMP table:

  select ename, job   from emp   where deptno = 10  ENAME      JOB  ---------- ---------  CLARK      MANAGER  KING       PRESIDENT  MILLER     CLERK 

Solution

Find and use the built-in function provided by your DBMS to concatenate values from multiple columns.

DB2, Oracle, PostgreSQL

These databases use the double vertical bar as the concatenation operator:

 1 select ename||' WORKS AS A '||job as msg 2   from emp 3  where deptno=10 

MySQL

This database supports a function called CONCAT:

 1 select concat(ename, ' WORKS AS A ',job) as msg 2   from 3  where deptno=10 

SQL Server

Use the "+" operator for concatenation:

 1 select ename + ' WORKS AS A ' + job as msg 2   from emp 3  where deptno=10 

Discussion

Use the CONCAT function to concatenate values from multiple columns. The || is a shortcut for the CONCAT function in DB2, Oracle, and PostgreSQL, while + is the shortcut for SQL Server.