SQL CONCAT

Introduction to SQL CONCAT

CONCAT() is a string function in standard query language (SQL) that is used to concatenate or combine two or more strings specified as arguments in the function. However, when concatenating, NULL arguments are ignored. Thus, the function helps in presenting data fields in a more readable format. There is one more version of the CONCAT() function supported in some SQL databases called CONCAT_WS() where all specified arguments except NULL values are concatenated together like CONCAT() but with the difference that the first argument is considered as a separator.

We can even use ‘||’ operator for concatenating two or more strings into one. The difference between CONCAT() function and ‘||’ operator is that the operator does not ignore NULL arguments.

Syntax and parameters

The basic syntax for using the CONCAT() function in SQL is as follows:

CONCAT(str_1, str_2, str_3, ...)

The parameters used in the syntax are:

• str_1, str_2, str_3, … : This is the list of arguments that has to be concatenated together. The arguments for CONCAT() function have to be of string convertible type, let us say, Text, VARCHAR, CHAR, etc.

The syntax for CONCAT_WS() function is as follows :

CONCAT_WS(separator,str_1, str_2, str_3, ...);

The parameters used in the above syntax are as follows :

• Separator: The first argument is considered as a separator which will separate subsequent arguments.

The rest of the arguments are similar to the CONCAT() function.

Having discussed the syntax and parameters for CONCAT() function in SQL, let’s dive in and try some examples based on it.

Examples of SQL CONCAT

Here are some basic examples illustrating the use of CONCAT() and CONCAT_WS() functions.

Example #1

SQL query to illustrate CONCAT() function.

Code:

SELECT CONCAT ('THIS',' ','IS',' ','AN',' ', 'EXAMPLE');

Output:

Example #2

SQL query to illustrate the use of the CONCAT_WS() function.

Code:

SELECT
CONCAT_WS (',','APPLE','BANANA','ORANGES','GRAPES')
AS FRUITS_BASKET;

Output:

Example #3

SQL query to illustrate the use of concatenation operator ||.

Code:

SELECT 'Hello' || ' ' || 'There' AS concatenated_string;

Output:

To illustrate the uses of the CONCAT() and CONCAT_WS() function, let us create a dummy table called “students.” This table contains details such as a student’s first name, last name, and date of birth. For example, we can use the following code snippet to create the “students” table.

CREATE TABLE students
(
student_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
first_name character varying(255) NOT NULL,
last_name character varying(255) NOT NULL,
birth_date date NOT NULL,
PRIMARY KEY (student_id)
);

We have successfully created the “students” table. Now let us insert some records in it to work with. Use the following piece of code for inserting values.

INSERT INTO students(student_id, first_name, last_name, birth_date)
VALUES(1,'Kritika','Sharma', TO_DATE('May 01 2000','Mon DD YYYY'));
INSERT INTO students(student_id, first_name, last_name, birth_date)
VALUES(2,'Rohit','Verma', TO_DATE('070798','MMDDYY'));
INSERT INTO students(student_id, first_name, last_name, birth_date)
VALUES(3,'Ariel','Winter', TO_DATE('2001-02-20 13:27:18', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO students(student_id, first_name, last_name, birth_date)
VALUES(4,'Mathew','Jones', TO_DATE('2020-52-5', 'IYYY-IW-ID'));
select * from students;

The final table after record insertions look something like this :

We are now all set to try some examples based on this table.

Example #4

The names of students are stored in parts as first_name and last_name in the student’s table. We have been tasked to display a student’s name as a single field instead of two fields.

Code:

SELECT CONCAT(first_name, ' ', last_name) as student_name
FROM students;

Output:

Here we have used the CONCAT() function to concatenate first_name, last_name, and space to return a student’s full_name.

Example #5

Suppose we have to display the full name of a student along with his age. The age should be declared in the form of a sentence such as “Age of Kritika is 20”.

Code:

SELECT CONCAT(first_name,' ',last_name),
CONCAT('Age of ', first_name,' is ',
EXTRACT(Year FROM '2020-06-01' ::date)- EXTRACT(Year FROM birth_date :: date))
FROM students;

Output:

In this example, we have first deduced a student’s age and then concatenated it using the CONCAT() function with the rest of the string.

Example #6

Suppose we have to display the full name of a student in a format where first_name and last_name are displayed together but separated by a comma.

Code:

SELECT CONCAT_WS(' , ',first_name,last_name) as comma_separated
FROM students;

Output:

This example is straightforward to understand as we have just replicated example 2 here with the only difference that there we had fruit names as arguments and here we have first_name and last_name.

Example #7

Suppose we have to find the two eldest students in the class. Along with that, we have to display the first_names of the eldest students in a single sentence with a comma as the separator.

Code:

SELECT CONCAT('The eldest students are ',
CONCAT_WS(' , ', first_name,
LEAD(first_name,1) OVER
(ORDER BY birth_date ASC)))
FROM students
ORDER BY birth_date ASC
LIMIT 1;

Output:

Here we have first found the first_names of the eldest and the next eldest students using ORDER BY and LEAD function respectively. Next, we have concatenated the results obtained together using CONCAT() and CONCAT_WS() functions.

Example #8

Suppose we have to display the full_names of all the students, but we are not allowed to use CONCAT() and CONCAT_WS() functions.

Code:

SELECT (first_name || ' ' || last_name) as student_name
FROM students;

Output:

In such cases, we can use the concatenation operator  ‘||’ to club the first_name and last_name fields together.

Conclusion

Functions like CONCAT() and CONCAT_WS() are used in SQL to concatenate two or more string convertible arguments into a single field. It is very useful for displaying results in such a manner that it becomes easily readable for a layman.

Recommended Articles

This is a guide to SQL CONCAT. Here we discuss the Examples of  SQL CONCAT illustrating the use of CONCAT() and CONCAT_WS() functions. You may also have a look at the following articles to learn more –

1. MySQL REGEXP
2. PostgreSQL Vacuum
3. MySQL REPLACE
4. PostgreSQL UNIQUE Index