SQL query: Concatenating Column Values (DB2, Oracle, PostgreSQL, MySQL, SQL Server)

SQL studing: 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 emp
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.

 

 

Вас заинтересует / Intresting for you:

A Brief History of SQL
A Brief History of SQL 2871 views Александров Попков Wed, 17 Oct 2018, 15:04:29
Referencing an Aliased Column ...
Referencing an Aliased Column ... 253 views Денис Wed, 14 Jul 2021, 12:59:03
SQL: how to determine which ro...
SQL: how to determine which ro... 178 views Денис Tue, 06 Jul 2021, 18:47:53
How to Retrieving a Subset of ...
How to Retrieving a Subset of ... 254 views Денис Wed, 14 Jul 2021, 04:45:37