SQL CONCAT() Function
FREE
Overview
A single string can be created by combining two or more strings using the SQL function CONCAT. It enables the concatenation of strings with various data types and can be used with literals, columns, or variables.
What is the CONCAT Function in SQL?
The CONCAT function in SQL is used to combine or merge two or more strings. The main purpose of this function is to concatenate two or more strings to make a single string. This function can take multiple strings as a parameter, but at least two strings are needed.
As you can see from the image above, the CONCAT function takes multiple strings as a parameter, and the output of the function is the concatenation of all the input strings.
NOTE: The CONCAT() function can take up to 255 input strings and combine them into one. It requires at least two input strings. If you pass one input string, then the CONCAT() function will raise an error.
Syntax of CONCAT in SQL
The syntax of the concat function is:
Here, string_1, string_2 ... string_n are the strings which are needed to be merged together.
NOTE: If any of the strings in the parameter above is NULL, then the resultant string will also be NULL as the output of the concat function in SQL.
In this article, we will dive deep into the syntax, examples and other details of the CONCAT function in SQL.
Examples of CONCAT in SQL
Let's take a few example cases to understand the use of the concat function in SQL.
Case 1:
Let's suppose we want to add a few strings and display something:
The output for the above query would be:
| Result |
|---|
| I am learning the concat function in SQL! |
Case 2:
Let's take another example of concatenating three literal strings to obtain the full name of a person.
The output for the above query would be:
| Result |
|---|
| Kanchan Jeswani |
How to Concatenate Two Columns in SQL?
We can also use the SQL CONCAT() function to concatenate two columns into one. It takes the column names as the parameters and returns a column with value after concatenating all the values of the columns passed as the parameters to the CONCAT function.
The syntax of this will be:
Let's understand this with the help of an example:
Suppose you have a student_details table, having ROLL_NO, FIRST_NAME, and LAST_NAME as the fields, and some data has been stored in these fields.
Now, let's see how our table's data looks by making use of the SELECT command.
NOTE: * represents all the columns, which means we are selecting or retrieving all the data from the given table.
OUTPUT:
| ROLL_NO | FIRST_NAME | LAST_NAME |
|---|---|---|
| 1 | Rita | Goyal |
| 2 | Kanika | Mittal |
| 3 | Jyoti | Sharma |
| 4 | Reshma | Kumari |
| 5 | Jayana | Meena |
| 6 | Devansh | Kumar |
| 7 | Kareena | Bhatt |
Now, let's say we want to concatenate the first and last names of the students and represent them in the form of a new column.
For this, we can write the query:
OUTPUT:
| ROLL_NO | FIRST_NAME | LAST_NAME | FULL_NAME |
|---|---|---|---|
| 1 | Rita | Goyal | Rita Goyal |
| 2 | Kanika | Mittal | Kanika Mittal |
| 3 | Jyoti | Sharma | Jyoti Sharma |
| 4 | Reshma | Kumari | Reshma Kumari |
| 5 | Jayana | Meena | jayana Meena |
| 6 | Devansh | Kumar | Devansh Kumar |
| 7 | Kareena | Bhatt | Kareena Bhatt |
As we can see from the output table above, the new field or the attribute has been added in the student_details table as the FULL_NAME, which is storing the concatenation of the FIRST_NAME and the LAST_NAME fields.
Let's take another example in which we will try to concat date-time fields in the form of a string. Suppose you have an employee table, where we have the fields like employee_id, joining_date, and the employee_name, and some data has been stored in these fields.
Now, let's see how our table's data looks by making use of the SELECT command.
OUTPUT:
| employee_id | employee_name | joining_date |
|---|---|---|
| 1 | Rita | 2007-01-24 |
| 2 | Kanika | 2007-05-27 |
| 3 | Jyoti | 2007-05-06 |
| 4 | Reshma | 2007-04-06 |
| 5 | Jayana | 2007-02-06 |
Now, let's say we want to concatenate all the information of an employee together and store them in the form of a new column named employee_information.
For that, we can write the query:
OUTPUT:
| employee_id | name | joining_date | employee_information |
|---|---|---|---|
| 1 | Rita | 2007-01-24 | 1 Rita 2007-01-24 |
| 2 | Kanika | 2007-05-27 | 2 Kanika 2007-05-27 |
| 3 | Jyoti | 2007-05-06 | 3 Jyoti 2007-05-06 |
| 4 | Reshma | 2007-04-06 | 4 Reshma 2007-04-06 |
| 5 | Jayana | 2007-02-06 | 5 jayana 2007-02-06 |
As we can see from the output table above, the new field or the attribute has been added in the employee table as the employee_information, which is storing the concatenation of the employee_id, employee_name and the joining_date fields.
Learn more about: advanced concepts of SQL.
Conclusion
- The CONCAT function in SQL is a string function that is used to merge two or more strings. The main purpose of this function is to concatenate two or more strings to make a single string.
- The CONCAT function requires at least two parameters, and this function can accept a maximum of 255 parameters.
- If any of the strings in the parameter above is NULL, then the resultant string will also be NULL as the output of the CONCAT function in SQL.
- CONCAT implicitly converts all the arguments to string values before concatenation. For example, if you pass a non-string value as a parameter in the CONCAT() function, then the CONCAT() function will implicitly convert that value into a string before concatenating.