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:

IDItem_NameItem_QuantityItem_PricePurchase_Date
1Soap52002021-07-08
2Toothpaste2802021-07-10
3Pen10502021-07-12
4Bottle12502021-07-13
5Brush3902021-07-15
6Notebooks1010002021-07-26
7Handkerchief31002021-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.






Latest Courses