Concat in SQL

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.

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

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 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.

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

Code:

Select concat(fname,lname) from stu_data_1;

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)

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;

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;

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:

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:

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

Code:

SELECT CONCAT(NULL,NULL) AS Result_out;

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;

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;

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!