Home

Learning Hub

Articles

CONCAT in SQL – A Definitive Guide to Use the Function

In programming, a string represents a sequence of characters used for text. In SQL Server, string data types include Character strings and Unicode character strings. String functions operate on string values, irrespective of data types, offering developers a variety of built-in options in SQL Server. Some of the popular string functions are Upper, Lower, Concat, Stuff, Substring, Replace, Reverse, Left, and Right. In this article, you will explore CONCAT in SQL.

What is CONCAT in SQL?

In SQL, the CONCAT operation serves to merge two or more strings into one cohesive string. Its primary objective is to amalgamate multiple strings into a singular entity. While this function is capable of handling multiple strings as input, it necessitates a minimum of two strings to function effectively.

Syntax for CONCAT in SQL

The syntax of the concat function is: 

CONCAT(string_1, string_2, …….string_n)

In this context, “string_1”, “string_2”, …, “string_n” represent the strings intended for merging.

Please take note: Should any of the strings within the parameter be NULL, the resulting string will also be NULL, as per the output behaviour of the CONCAT function in SQL.

Also read: Everything About SQL View with Examples

Examples of CONCAT in SQL

Now, let us look into a few examples to better the CONCAT function in SQL.

Case 1:

Let’s suppose we want to add a few strings and display something:

SELECT CONCAT(“I “, “am “, “learning “, “the”, “concat “, “function “, “in “, “SQL!”) AS Result;

Output:

I am learning the concat function in SQL!

Case 2:

Consider another instance where three literal strings are combined to form the complete name of an individual.

SELECT CONCAT(“Kanchan”, ” “, “Jeswani “) AS Full_Name;

Output:

Kanchan Jeswani

Code:

create table stu_data_1(rno numeric(11), fname varchar(30), lname varchar(30))

insert into stu_data_1 values(11,’Anu’,’sharmaa’)

insert into stu_data_1 values (13,’varc’,’arun’)

select * from stu_data_1

Then, concatenate the fname and lname from the table stu_data_1.

Code:

Select concat(fname,lname) from stu_data_1;

Concatenating the Numerical Data Using the Concat Function

In SQL, numeric data from tables can be concatenated akin to string concatenation. The CONCAT function facilitates joining numeric values. 

Let’s illustrate with three integer values:

Code:

SELECT CONCAT(11,12,13)

An alternative method to employ Concat in SQL with numerical values involves utilising the CAST operator. This operator transforms numerical data into string format. The + (plus) operator then facilitates manipulating numeric data for string concatenation. 

It’s imperative to convert numerical data into a string data type; otherwise, the concatenation operation may yield incorrect results.

Code:

SELECT (11+12+13) as Result;

Here, the 11,12,13 are numerical values. The plus operator will perform an additional operation.

 To overcome this issue, we use the CAST operation.

Code:

SELECT (CAST(11 AS VARCHAR)+CAST(12 AS VARCHAR)+CAST(13 AS VARCHAR)) as Result;

An implicit conversion occurs when the CONCAT function in SQL receives a parameter of a non-string data type.

Also read: COALESCE in SQL

Wrapping It Up

The CONCAT operation in SQL effectively merges strings into a single entity, requiring at least two strings to function. When any parameter within the CONCAT function is NULL, the result is NULL. Examples demonstrate its usage, such as concatenating strings and handling table data.

Numeric data can also be concatenated, either directly or by using the CAST operator to ensure correct outcomes. Implicit conversions take place if non-string parameters are passed to CONCAT. To learn better, join the Accelerator Program in Business Analytics and Data Science.