CONCAT Function in SQL: Master SQL Server CONCAT Function with Examples

Updated on December 19, 2024

•

3 min read

ARTICLE OUTLINE

What is CONCAT in SQL?Syntax for CONCAT in SQL Examples of CONCAT in SQL CONCAT Function in Table Data Concatenating the Numerical Data Using the Concat Function Wrapping It Up FAQs

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.

POSTGRADUATE PROGRAM IN

Multi Cloud Architecture & DevOps

Master cloud architecture, DevOps practices, and automation to build scalable, resilient systems.

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

CONCAT Function in Table Data

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;

82.9%

of professionals don't believe their degree can help them get ahead at work.

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.

FAQs

What is the CONCAT function in SQL?

The CONCAT function combines multiple string arguments into one string, necessitating a minimum of two inputs. It automatically converts all inputs to strings and treats null values as empty strings.

What is the use of concat ()?

The CONCAT function merges text from various ranges or strings but lacks delimiter or IgnoreEmpty parameters. It replaces the CONCATENATE function.

How do I concatenate two cells in SQL?

In SQL, concatenation commonly employs either the CONCAT function or the '+' operator, depending on the database system. Each approach facilitates merging column values, offering flexibility tailored to the database's needs.

Updated on December 19, 2024

Link