The SQL CONCAT function for string concatenation

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.

For example:

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:

Query:

SELECT CONCAT ( 'Date ', 'of Birth', ': ', 23, ' ', 'September,', '1979' )

AS "Birth Date";

The result:

SQL CONCAT

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;

The result-set:

SQL CONCATENATION

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 recordset:

SQL CONCAT DATE

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;

The result:

SQL-CONCAT numbers

Using a condition with CONCAT function

You may also filter the rows returned as using the CONCAT function. For filtering the resultsets, use the WHERE clause with the simple SELECT statement or HAVING clause with aggregated data.

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);

The output:

SQL CONCAT WHERE

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";

 

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!