The SQL CONCAT function joins two or more strings together to form a single string. The CONCAT function contains at least two parameters and can accept a total of 254 parameters.

String Functions in SQL

In programming languages, a string is a set of characters that is used to declare texts. In terms of SQL Server, SQL string data types can be divided into two classes - Character strings and Unicode character strings.

PCP in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
PCP in Business Analysis

A string function accepts a string value as input and returns a string value regardless of the data type. Developers can use many of SQL Server's built-in string functions.

Some of the popular string functions are listed below:

  • Upper
  • Lower
  • Concat
  • Stuff
  • Substring
  • Replace
  • Reverse
  • Left
  • Right

Syntax:

CONCAT(string_Val1, string_val2, ...., string_valn);

Code:

select CONCAT('hai','hello','welcome','SQLturorial');

In this above example, the strings hai, hello, welcome and SQL tutorial have been concatenated with the help of the CONCAT function in SQL.

Concat_in_SQL_1 

Concat Function in Table Data

For Concat in SQL, use the same concat function for easy access of the tabled record within a few lines of instructions.

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.

Concat_in_SQL_2.

Free Course: Introduction to SQL

Learn MySQL, PostgreSQL and SQL ServerEnroll Now
Free Course: Introduction to SQL

Code:

Select concat(fname,lname) from stu_data_1;

Concat_in_SQL_3 

Concatenating the Numerical Data Using the Concat Function

In SQL, you can also concatenate numerical data from the table in the same way as we concatenate strings. The CONCAT function can also be used to join numeric values.

We'll join three different integer values in the following example:

Code:

SELECT CONCAT(11,12,13)

Concat_in_SQL_4

Another way to implement Concat in SQL with the numerical value is to use the CAST operator. This operator converts the numerical data into the string format. Using the + (plus) operator will manipulate the numeric data into string concatenation.

You must convert the numerical data into a string data type. Otherwise, the concatenation operation's outcome would be incorrect:

Code:

SELECT (11+12+13) as Result;

Concat_in_SQL_5.

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;

Concat_in_SQL_6

Remarks:

An implicit conversion is performed if any non-string data type parameter is passed to the CONCAT function in SQL.

CONCAT Function With Null Value

In SQL Server, NULL is a special pointer that specifies a value that is undefined or does not exist. When passing a NULL value as a parameter to the CONCAT function, the NULL values are converted to an empty string.

Let's take a look at an example:

Code:

SELECT CONCAT('good',NULL,'Day') AS Result_out;

In this example, we are trying to concatenate the good and day with a null value. This type of NULL will be used to act as an empty space of a string.

Output:

Concat_in_SQL_7

As can be seen, the NULL expression in the CONCAT function had no effect on the function's result and behaved like an empty string. Furthermore, if all parameters are NULL, the function's output would be an empty string. Let's take a look at an example:

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

Code:

SELECT CONCAT(NULL,NULL) AS Result_out;

Concat_in_SQL_8.

As a consequence, we can assume that NULL values have no effect on the function's performance.

Line Feed(\n) in CONCAT Function

Converting ASCII numbers to character values is possible with the CHAR function. With the CHAR function in SQL, the following ASCII codes can be used to generate a new line:

When we concatenate the strings with the following CHAR functions, we get a new line:

CHAR(10) is the ASCII code for the Line feed character. Use this code for the concatenation function in SQL.

Code:

SELECT CONCAT('this',CHAR(10),'year',CHAR(10),'good year') AS Result_out;

Concat_in_SQL_9

Carriage Return(\r) in Concat Function in SQL:

In a Windows environment, both r and n are used to add a line break, while in a Linux environment, only n is used, and in a Mac environment, only r is used. I'll teach you how to add and delete line breaks in SQL syntax in this article. The examples below are written for a Windows environment, but you can change them to a Linux environment by removing all of the r in the syntax. CHAR(13) is the ASCII code for the Carriage return character. Use this code for the concatenation function in SQL.

Code:

SELECT CONCAT('this',CHAR(13),'year',CHAR(13),'good year') AS Result_out;

Concat_in_SQL_10

Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!

Conclusion

Concatenation is very useful when you want to display a message that includes the value of a variable. Using different examples, you learned how to use the CONCAT function in SQL. While SQL is an old query language, it is still very popular today among businesses worldwide to gather and process a vast volume of data to take business actions in realtime in order to expand. SQL is regularly among the most-requested tech skills, and mastering it will greatly enhance your skill set.

In this article, you have learned about indexes with syntax and examples. To gain more expertise in the SQL programming language, please join our Simplilearn's SQL Certification Training Program. 

Simplilearn has made a special selection of courses available for free for all the learners. Please explore the courses here and find the categories of the subjects covered. All these courses include a substantial amount of content developed by renowned global experts. Simply choose a course and start learning.

Do you have any questions for us? Leave them in the comments section of this article, and our experts will get back to you on them, as soon as possible!

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.
  • *According to Simplilearn survey conducted and subject to terms & conditions with Ernst & Young LLP (EY) as Process Advisors