concat SQL | concatenate in SQL - sql - sql tutorial - learn sql
- String concatenation means to append one string to the end of another string.
- SQL allows us to concatenate strings but the syntax varies according to which database system you are using.
- Concatenation can be used to join strings from different sources including column values, literal strings, output from user defined functions or scalar sub queries etc.
- The Concatenate function combines multiple character strings together. Each database provides its own way(s) to do this:
- MySQL: CONCAT( )
- Oracle: CONCAT( ), ||
- SQL Server: +
Syntax:
The syntax for CONCAT () is as follows:
CONCAT (str1, str2, str3, ...)- The above syntax concatenates str1, str2, str3, and any other strings together.
- Each str can be a column name, or it can be a literal character string (meaning a sequence of characters enclosed by two single quotes), or just white space.
- Please note the Oracle CONCAT( ) function only allows two arguments -- only two strings can be put together at a time using this function.
- However, it is possible to concatenate more than two strings at a time in Oracle using '||'.
The syntax for using '||' to concatenate is as follows:
str1 || str2 || str3 ...The syntax for using '+' to concatenate is as follows:
str1 + str2 + str3 ...
Examples:
We use the following table for our examples.
Table Geography
| Region_Name | Store_Name |
|---|---|
| East | Boston |
| East | New York |
| West | Los Angeles |
| West | San Diego |
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExample 1: Use CONCAT function to concatenate
Example 1: Use CONCAT function to concatenate
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialMySQL/Oracle:
MySQL/Oracle:
SELECT CONCAT(Region_Name, Store_Name) FROM Geography
WHERE Store_Name = 'New York';Result:
'East New York'sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExample 2: Use '||' to concatenate
Example 2: Use '||' to concatenate
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialOracle:
Oracle:
SELECT Region_Name || ' ' || Store_Name FROM Geography
WHERE Store_Name = 'Boston';
Result:
'East Boston'Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExample 3: Use '+' to concatenate
Example 3: Use '+' to concatenate
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialSQL Server:
SQL Server:
SELECT Region_Name + ' ' + Store_Name FROM Geography
WHERE Store_Name = 'Boston';
Result:
'East Boston'