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

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:

Table: REGION

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

Table: BRANCH

branch_nbr	branch_name	region_nbr	employee_count
108	New York	100	10
110	Boston	100	6
212	Chicago	200	5
404	San Diego	400	6
415	San Jose	400	3

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)