SQL Basics | Functions | Concatenate
Sometimes you need to combine data from different fields. Each database has a way to achieve this.
- MySQL: CONCAT()
- Oracle: CONCAT(), ||
- SQL Server: +
Concatenate syntax
CONCAT() is used as follows.
CONCAT(string1, string2, string3, ...)
It combines string1, string2, string3, and so on. Note that Oracle’s CONCAT() accepts two parameters. In other words, it can combine only two strings at a time. To combine multiple strings at once, use ||.
Concatenate example
For example, suppose you have the following table.
geography table
| region_name | store_name |
|---|---|
| East | Boston |
| East | New York |
| West | Los Angeles |
| West | San Diego |
Example 1
MySQL / Oracle:
SELECT CONCAT(region_name, store_name) FROM geography
WHERE store_name = 'Boston';
The result is as follows.
'EastBoston'
Example 2
Oracle:
SELECT region_name || ' ' || store_name FROM geography
WHERE store_name = 'Boston';
The result is as follows.
'East Boston'
Example 3
SQL Server:
SELECT region_name + ' ' + store_name FROM geography
WHERE store_name = 'Boston';
The result is as follows.
'East Boston'