The CONCAT function is used to combine or concatenate two or more string values. The SQL CONCAT function takes at least two arguments that are converted to strings (if they are not already) and returns a concatenated string.
CONCAT ( ‘String 1 ‘, ‘String 2 ‘, ‘String 3’, ‘String N’ );
Using column names:
CONCAT ( first_name_col, ‘ ‘, Second_Name_col);
The next section shows you working queries of using the CONCAT function in SQL databases (MS SQL Server and MySQL).
The example of combining string values
The first example shows using the constant values in the CONCAT function for concatenating a few strings. I have used strings, number, and an empty string in the query:
SELECT CONCAT ( 'Date ', 'of Birth', ': ', 23, ' ', 'September,', '1979' ) AS "Birth Date";
Note: If you provide one argument in the CONCAT SQL function then it will produce an error. In SQL Server, you may provide from 2 to 254 arguments.
Concatenating the table column values
In this example, I have used the employees’ table that stores data like employee name, age, salary etc. In the SELECT statement, the CONCAT function is used for combining the whole table column values as follows:
The CONCAT query with table columns:
SELECT CONCAT ('Employee Name: ', emp_name, ' AND ','Age = ', emp_age) AS "Employee Info" FROM sto_employees;
Concatenating a date column example
You may also use datetime columns in the CONCAT clause for getting the concatenated result. In this query, we will get the resultset of employees with their names and join date.
SELECT CONCAT ('Employee Name: ', emp_name, '.','Joining Date = ', join_date) AS "Name and Join Date" FROM sto_employees;
The 6th record contains the NULL value for the join_date column, however, the CONCAT function converts the NULL value into an empty string; no error is raised.
How about concatenating a numeric column?
You may also use the numeric column in the CONCAT function. As mentioned earlier, the CONCAT converts the numbers into strings and combines the string.
In our example table of employees, we have a numeric column (emp_salary). For showing the usage of the numeric column, I have concatenated the numeric column for showing employee’s salaries along with their names.
The “$” sign is also concatenated for showing the salaries in dollars. Have a look at the query and output:
SELECT CONCAT ('Name: ', emp_name, '||',' Salary = $', emp_salary) AS "Salary Information" FROM sto_employees;
Using a condition with CONCAT function
In this query, I will return concatenated results only for specific employees by using IDs in the WHERE clause with the IN operator:
SELECT id, CONCAT ('Employee: ', emp_name, '||',' Salary = $', emp_salary, '& Age is ', emp_age) AS "Salary Information" FROM sto_employees Where id IN (1,3,5,7);
Concatenating with a delimiter by CONCAT_WS function
There is another function for concatenation in SQL Server and MySQL database. This is called CONCAT_WS function which is used for concatenating strings by a delimiter e.g. a comma, hyphen etc.
The first argument in the CONCAT_WS function is the delimiter as shown in the syntax below:
CONCAT_WS(separator, arg1, arg2, arg3,…)
See an example query of using the function:
SELECT CONCAT_WS(' - ','Wheat', NULL, '$20', '200 bags') AS "Wheat Info";