SQL Concatenate Function

Concatenation, in simple terms, is defined as combining a group of strings to a single string. When it comes to SQL, Concatenation is specified by the CONCAT() function. There should be a total of 255 input strings to combine them together. Today in this tutorial, we will check the use of function CONCAT() in SQL, and concatenation SQL in depth.

What do you mean by the function CONCAT() in SQL?

As said earlier, the concatenation of the string is made possible through the CONCAT() function. There are several factors you need to know when making use of the CONCAT function in SQL. Here are some of them below.

• Let’s say you are passing one string as input; in this case, the CONCAT function pops up an error message. It means the CONCAT function works well without any troubles if you have at least two input strings.
• The CONCAT function also accepts string values as input. It will convert the string values implicitly before the process of concatenation.
• As discussed earlier, the maximum input string that the CONCAT functions accept is 255 input strings.
• The CONCAT function also makes use of the type VARCHAR(1) to convert any null into an empty string.
• You can make use of the CONCAT_WS() function if you need to add a separator during the process of concatenation.

Let me show the syntax of using the CONCAT function.

CONCAT (input string1, input string2 [,input string]);

How to make use of CONCAT() function in SQL?

Let me explain the concept with a simple example.

Let’s consider we are now having two strings, namely “Gangboard” and “SQL”. Now, if we concatenate the above two strings, we get a concatenated string or resultant string as “Gangboard SQL”. The same process is followed in the CONCAT function too. Below is the command to concatenate two string “Gangboard” and “SQL”.

SELECT CONCAT(“Gangboard”, “SQL”);

The output of the concatenate function will be

GangboardSQL.

We can also make use of the ‘+’, which is the addition operator to add the strings together. Here is the command for adding two or more strings.

SELECT “Gangboard” + “SQL”;

The Output will be

GangboardSQL

We can make use of the CONCAT_WS() function to separate the two or more strings with a separator. Let’s consider a simple example to that you get a clear understanding of it.

SELECT CONCAT_WS(“-”, “GANGBOARD”, “SQL”);

The output of this will be:

GANGBOARD-SQL

The above are some of the approaches you can make use to concatenate strings in SQL. There are also other parameters that we use it to pass to the CONCAT() function.

Concatenation Parameters:

Let’s check out some of the Concatenation parameters below.

• Addition Operator Parameter: The parameter of this type just requires two or more strings, and this should be separated by a comma for the concatenation process to occur.
• CONCAT Parameter: The parameter required for the concatenation process is the string value, and a comma should also separate this.
• CONCAT_WS Parameter: The first parameter you need to use is the separator and then the strings for the concatenation process. A comma should separate each of the parameters.

CONCAT Function examples:

Here is an example to understand the literal string parameter.

SELECT ‘gangboard’ + ‘SQL’ as full_name;

The output will be:

besanttechnologiesSQL

Another example can be

SELECT CONCAT(‘gangboard’, ‘sql’);

The output will be:

gangboardsql

How to use CONCAT work with table values?

Let’s understand how to make use of the table values for the working of concatenation.

Here is the command of how you concatenate both the first and last names.

SELECT first name, last name, 
CONCAT(first name, ‘ ‘, last name)full name
FROM N
ORDER BY full name

The output will be

How to use CONCAT() with null values?

Let’s consider we have some null values in the table. The Concat function makes use of the empty for the process of concatenation if the value is null. Let’s check the commands.

SELECT first name, last name, ID,
CONCAT(first name, ‘ ‘, last name, sex)full name
FROM N
Order by full name

The output will be

I hope the above tutorial helped you to gain complete knowledge on Concatenation in SQL and how to use them effectively. In case of any queries, let us know through the comment section below.

Related Blogs

1. SQL Joins
2. Schema in SQL
3. Decode in SQL