SQL CONCAT function concatenates two strings to form a single string. SQL CONCAT function is used to join two or more strings. It takes up to 255 input strings which are further joined by the function.
For performing a join operation, CONCAT requires at least 2 strings. It will raise an error if provided with only one string.
If any non-character string values are passed in the function, they will be implicitly converted to strings before concatenating. Likewise, if any NULL is added to the function, it converts it into an empty string with VARCHAR (1).
Syntax
SELECT CONCAT (string1, string2, ... string_n)
Parameters
CONCAT: This is the function that is used for concatenating.
string1, string2, … string_n: These are the strings used for concatenating.
See the following query.
Select concat (‘Appdividend’, ‘.com’);
See the output.
Appdividend.com
See the query.
Select concat (‘App’, ‘Div’, ‘idend’, ‘.com’);
See the output.
Appdividend.com
See the following query.
Select concat ('Love', ' ', 'is', ' ', 'Life');
See the output.
Love is Life
Here we used space as another character to concatenate with the words. Adding space prevents our values from being squished together.
See the following query.
SELECT CONCAT ('Let', '''', 's learn MySQL');
See the output.
Let's learn MySQL
In the above example, we have used the second parameter within the CONCAT function to add a single quote into the middle of the resulting string.
Above are the common examples to make clear how the concat function works.
Let’s see the example with proper tables.
Consider table Employee
| Emp_id | First_name | Last_name | City | State | Salary |
| 101 | Rohit | Raj | Patna | Bihar | 30000 |
| 201 | Shivam | Rana | Jalandhar | Punjab | 20000 |
| 301 | Karan | Kumar | Allahabad | Uttar Pradesh | 40000 |
| 401 | Suraj | Bhakat | Kolkata | West Bengal | 60000 |
| 501 | Akash | Cherukuri | Vizag | Andhra Pradesh | 70000 |
Suppose we want to concatenate first_name and last_name under the single column name Emp_name. Then the following query has to be used.
See the following query.
Select Concat (First_name, ' ', Last_name) AS Emp_name from Employee;
See the output.
| Emp_id | Emp_name | City | State | Salary |
| 101 | Rohit Raj | Patna | Bihar | 30000 |
| 201 | Shivam Rana | Jalandhar | Punjab | 20000 |
| 301 | Karan Kumar | Allahabad | Uttar Pradesh | 40000 |
| 401 | Suraj Bhakat | Kolkata | West Bengal | 60000 |
| 501 | Akash Cherukuri | Vizag | Andhra Pradesh | 70000 |
Here you can see that names were concatenated under a single column name Emp_name. Here we have also used Space as an additional character to add space between two names to avoid confusion between the name.
That’s it.
