SQL ConcatenateThere 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.
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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. Next TopicSQL get month from the date |