SQL Concatenate
There may arise a situation when you need to join the strings stored in two different columns of a single table. To connect such strings, string concatenation is performed in SQL using the CONCAT() function.
- More than two strings can also be combined together to form a single string.
- The strings which are to be combined can be stored within two different columns of a table, or they can exist individually as a string without being stored into a table.
- After concatenating the strings, which are stored in two different columns of a table, the final concatenated string can also be stored in the table column.
Let us see few practical examples to understand this concept more clearly. We will use the MySQL database for writing all the queries.
Consider we have the following strings:
Now, write a query to concatenate two strings to form a single string and store it in a variable.
Query:
Two string literals are passed to CONCAT () function. The concatenated string is stored in ‘FinalString’.
You will get the following output:
| FinalString |
|---|
| Hello Allen |
The final concatenated string is “Hello Allen” which is stored in ‘FinalString’.
Suppose we have multiple strings given as follows:
String 1: Excited
String 2: to
String 3: learn
String 4: SQL
String 5: database
Now, write a query to concatenate all the above strings to form a single string and store it in a variable.
Query:
Five-string literals are passed to CONCAT () function. The concatenated string is stored in ‘FinalString’.
You will get the following output:
| FinalString |
|---|
| Excited to learn SQL database |
The final concatenated string is “Excited to learn SQL database” which is stored in the ‘FinalString’.
We can also combine a string and a number literal to form a single string.
Let us see examples for this.
Query:
One string literal (SQL) and one number literal (1000) are passed to CONCAT () function.
You will get the following output:
| FinalString |
|---|
| SQL1000 |
The final concatenated string is “SQL1000” which is stored in ‘FinalString’.
Let us see few examples to concatenate the strings stored in different columns of a table.
Consider we have an items table with the following data:
| ID | Item_Name | Item_Quantity | Item_Price | Purchase_Date |
|---|---|---|---|---|
| 1 | Soap | 5 | 200 | 2021-07-08 |
| 2 | Toothpaste | 2 | 80 | 2021-07-10 |
| 3 | Pen | 10 | 50 | 2021-07-12 |
| 4 | Bottle | 1 | 250 | 2021-07-13 |
| 5 | Brush | 3 | 90 | 2021-07-15 |
| 6 | Notebooks | 10 | 1000 | 2021-07-26 |
| 7 | Handkerchief | 3 | 100 | 2021-07-28 |
Example 1:
Write a query to concatenate item names with their respective price stored in the items table.
Query:
The two strings stored in the columns ‘Item_Name’ and ‘Item_Price’ of the items table are passed as an argument to the CONCAT () function.
You will get the following output:
| Item_Details |
|---|
| Soap200 |
| Toothpaste80 |
| Pen50 |
| Bottle250 |
| Brush90 |
| Notebooks1000 |
| Handkerchief100 |
All the item names stored in the ‘Item_Name’ and the item cost stored in the ‘Item_Price’ are concatenated row-wise from the items table and stored in ‘Item_Details’. The SELECT query displays all the values stored in the ‘Item_Details’.
Example 2:
Write a query to concatenate item ids with their respective names stored in the items table.
Query:
The two strings stored in the columns ‘ID’ and ‘Item_Name’ of the items table are passed as an argument to the CONCAT () function.
You will get the following output:
| Item_Details |
|---|
| 1Soap |
| 2Toothpaste |
| 3Pen |
| 4Bottle |
| 5Brush |
| 6Notebooks |
| 7Handkerchief |
All the item ids stored in the ‘ID’ and the item names stored in the ‘Item_Name’ are concatenated row-wise from the items table and stored in ‘Item_Details’. The SELECT query displays all the values stored in the ‘Item_Details’.
Example 3:
Write a query to concatenate item ids with their names stored in the items table along with the space in between the item ID and item name.
Query:
The two strings stored in the columns ‘ID’ and ‘Item_Name’ of items table with a space in between is passed as an argument to the CONCAT () function.
You will get the following output:
| Item_Details |
|---|
| 1 Soap |
| 2 Toothpaste |
| 3 Pen |
| 4 Bottle |
| 5 Brush |
| 6 Notebooks |
| 7 Handkerchief |
All the item ids stored in the ‘ID’ and the item names stored in the ‘Item_Name’ are concatenated row-wise from the items table with a space in between and stored in ‘Item_Details’. The SELECT query displays all the values stored in the ‘Item_Details’.
Example 4:
Write a query to concatenate item names with their respective ids. A string literal ‘assigned to’ should be placed before each item id, and another string literal ‘as an item ID’ should be placed after every item id.
Query:
‘Item_Name’, ‘assigned to’, ‘ID’ and ‘as an item ID’ are passed as an argument to CONCAT () function, and the concatenated string is stored in ‘Item_Details’.
You will get the following output:
| Item_Details |
|---|
| Soap assigned to 1 as an item ID |
| Toothpaste assigned to 2 as an item ID |
| Pen assigned to 3 as an item ID |
| Bottle assigned to 4 as an item ID |
| Brush assigned to 5 as an item ID |
| Notebooks assigned to 6 as an item ID |
| Handkerchief assigned to 7 as an item ID |
All the item names stored in ‘Item_Name’ and item ids stored in ‘ID’ are concatenated row-wise from the items table along with the string literals ‘assigned to’ and ‘as an item ID’ before and after every item id, respectively. The entire concatenated string is stored in ‘Item_Details’ and is displayed by SELECT query.
Example 5:
Write a query to concatenate item names with their purchase date stored in the items table along with a string literal ” is purchased on ” in-between the item name and purchase date.
Query:
The two strings stored in the columns ‘Item_Name’ and ‘Purchase_Date’ of items table with a string literal ‘ is purchased on ‘ between the item name, and its purchased date is passed as an argument to the CONCAT () function.
You will get the following output:
| Item_Details |
|---|
| Soap is purchased on 2021-07-08 |
| Toothpaste is purchased on 2021-07-10 |
| Pen is purchased on 2021-07-12 |
| Bottle is purchased on 2021-07-13 |
| Brush is purchased on 2021-07-15 |
| Notebooks is purchased on 2021-07-26 |
| Handkerchief is purchased on 2021-07-28 |
All the item names stored in ‘Item_Name’ and the purchase dates stored in ‘Purchase_Date’ are concatenated row-wise from the items table, along with a string literal ” is purchased on ” in-between them are stored in ‘Item_Details’. The SELECT query displays all the values stored in ‘Item_Details’.
Example 6:
Write a query to concatenate item names with their respective quantities with a string literal ‘Cost of’ that should be placed before each item quantity, a space after item quantity, a string literal ‘is’ should be placed after every item name and further concatenate it with the respective item price.
Query:
You will get the following output:
| Item_Details |
|---|
| Cost of 5 Soap is 200 |
| Cost of 2 Toothpaste is 80 |
| Cost of 10 Pen is 50 |
| Cost of 1 Bottle is 250 |
| Cost of 3 Brush is 90 |
| Cost of 10 Notebooks is 1000 |
| Cost of 3 Handkerchief is 100 |
All the item quantities stored in ‘Item_Quantity’, item names stored in ‘Item_Name’, and item costs stored in ‘Item_Price’ are concatenated row-wise from the items table along with the string literals ‘Cost of ‘ and ‘is’ before concatenation of item quantity and item name. Further, ‘is’ is concatenated with the item cost. The entire concatenated string is stored in ‘Item_Details’ and displayed by SELECT query.