SQL Concatenation

David Haertzen David Haertzen, Tutorial Author
Check out our
Data Warehousing Tutorial.
Previous | Next

SQL > SQL Advanced > SQL Concat / Concatenation

What is SQL Concatenation?

Concatenation is the process of combining data strings.

For example, the concatenation of 'John', ' ', 'W', '. ' and 'Smith' is

'John W. Smith'

Also, the concatenation of '212', '-', '555', '-' and '1234' is


Why Use SQL Concatenation?

Concatenation can result in more readable output while maintaining data in separate columns for greater flexibility.

How To Use SQL Concatenation

The means of achieving concatenation differs by database type. for example:

  • MySQL uses the CONCAT() function
  • Oracle uses the CONCAT() function and the || operator
  • SQL Server uses the + operator

More than 2 strings can be combined through concatenation. Numbers must be converted to strings before concatenation.

SQL Concatenation Syntax

<value_1> + <value_2>

SQL Concatenation Example

The following example concatenates the column branch_name with column region_name along with spaces and parens for readability.

Here are the contents of the tables:

100East Region
200Central Region
300Virtual Region
400West Region

108New York10010
404San Diego4006
415San Jose4003

This SQL Statement with concatenation is executed:

SELECT branch.branch_name + ' (' + region.region_name + ')'
FROM dbo.region
JOIN dbo.branch
ON branch.region_nbr = region.region_nbr
ORDER BY region.region_nbr

Here is the result.

branch_name (region_name)
New York (East Region)
Boston (East Region)
Chicago (Central Region)
San Diego (West Region)
San Jose (West Region)

Copyright© 1999-2015, First Place Software, Inc.