In this article, we will explore the syntax, working mechanism and other details of the CONCAT function in SQL and we will also make up various different examples about it.
Introduction
A string is a set of characters that helps to declare the texts in the programming languages. In terms of SQL Server, we can categorize SQL string data types into different two groups. These are:
-
Character strings data types that store non-Unicode character data
Character strings data types
Data type
Min limit
Max limit
char
0 chars
8000 chars
varchar
0 chars
8000 chars
varchar (max)
0 chars
2^31 chars
text
0 chars
2,147,483,647 chars
-
Unicode character strings data types that store Unicode character data
Unicode character string data types
Data type
Min limit
Max limit
nchar
0 chars
4000 chars
nvarchar
0 chars
4000 chars
ntext
0 chars
1,073,741,823 char
We have to take into account one point about the text and ntext data types. These data types are deprecated, and for this reason, we should try not to use these data types. We can use nvarchar(max) or varchar(max) instead.
SQL string functions are used to manipulate the character expressions or to obtain various information about them. CONCAT function in SQL is one of the most useful members of these functions. CONCAT function is a SQL string function that provides to concatenate two or more than two character expressions into a single string. Now, we will go into the point with a simple example.
CONCAT function syntax
The syntax of the function looks like as follows:
CONCAT ( string_value1, string_value2 [, string_valueN ] )
The CONCAT function at least requires two parameters and this function can accept a maximum of 254 parameters.
CONCAT function examples
In this example, we will join Think and green strings with the CONCAT function:
|
1 |
SELECT CONCAT('Think','green') AS 'FullString' |
As we can see clearly in this first example, the CONCAT function joined these two strings and we obtained the Thinkgreen string.
In this second example, we will join 7 strings:
|
1 |
SELECT CONCAT('If' , ' you' , ' save', ' a', ' tree' , ' you' , ' save' ,' a' ,' life') AS 'FullString' |
In this second example, the CONCAT function concatenated more than two strings and the result was If you save a tree you save a life.
In addition, we can concatenate the variables with this function:
|
1 2 3 4 5 |
DECLARE @Str1 AS VARCHAR(100)='Think' DECLARE @Str2 AS VARCHAR(100)='-' DECLARE @Str3 AS VARCHAR(100)='green' SELECT CONCAT(@Str1,@Str2,@Str3) AS ResultString |
Concatenating numerical expressions with CONCAT function in SQL
CONCAT function also has the capability to join the numeric values. In the following example, we will join three different integer values:
|
1 |
SELECT CONCAT(11,33,99) AS Result |
As we can see, we did not use any CAST or CONVERT function to join these numerical expressions with the function. On the other hand, if we want to concatenate these expressions with (+) plus sign, we need to convert them to string data types. Otherwise, the result of the concatenation operation will be incorrect:
|
1 |
SELECT CAST(11 AS VARCHAR(10)) + CAST(33 AS VARCHAR(10)) +CAST(99 AS VARCHAR(10)) AS TrueResult |
The following example demonstrates the concatenating numerical expressions with (+) plus without any data conversion so the output will be a mathematical addition:
|
1 |
SELECT 11+33+99 AS WrongResult |
Now, let’s research and try to understand what is happening behind the scene while the numerical expressions concatenation process with CONCAT function.
Firstly, we will create a test table in order to insert some numerical expressions. The following script will create a Test_NumericValue table:
|
1 2 |
DROP TABLE IF EXISTS Test_NumericValue CREATE TABLE Test_NumericValue (Number_1 INT , Number_2 INT , Number_3 INT) |
In the second step, we will insert test data to this table:
|
1 |
INSERT INTO Test_NumericValue VALUES (11,33,99) |
Now, we will execute the below SELECT statement in the ApexSQL Plan:
|
1 |
SELECT CONCAT(Number_1,Number_2,Number_3) FROM Test_NumericValue |
In this step, we will analyze the actual execution plan details of the query. The reason why we analyze the execution plan is that all details about the query processing operation are hidden in it:
Let us briefly explain the query plan mentioned above. The table scan operator indicates that the query optimizer is required to read whole data of the Test_NumericValue because it does not contain any index. However, this isn’t a problem for this table because it contains only one row. On the other hand, if we find out this operator for any table which has a huge number of rows, this situation may indicate a performance issue. Compute Scalar operator performs a scalar computation and takes the task of completing the CONCAT function operation for this query execution. In the final step of the execution plan, the SELECT operator represents the result of the query. A warning sign shows on the SELECT operator. We will mention the details of this warning sign in the following tip. Now, we will click the Operations tab to find out more details about the Compute Scalar operator.
As we can clearly see, a data conversion occurred while the query was executing. Particularly for this query, the integer (INT) data type expressions are converted to strings (VARCHAR). With this example, we demonstrated that the data conversion operation is made by the CONCAT function. In the following tip, we will highlight another issue about the execution plan that is CONVERT_IMPLICIT operation.
Tip: CONCAT function in SQL performs implicit conversion if any non-string data type parameter passes.
As we mentioned above, there is a warning sign shown on the SELECT operator in the execution plan. The reason for this sign is that if we use any non-character parameters in the CONCAT function, these parameters will be converted into the string data type automatically by SQL Server. Then the string combining operation will be performed. Now, we will reinforce this theoretical information with an example:
Firstly, we will execute the following query in order to prepare the test environment:
|
1 2 3 4 5 6 7 |
DROP TABLE IF EXISTS PhoneNumbers CREATE TABLE PhoneNumbers (ClientName VARCHAR(100),AreaCode INT , PhoneNumber BIGINT,Dt DATETIME) INSERT INTO PhoneNumbers VALUES('Name-1',301,2929420,GETDATE()) INSERT INTO PhoneNumbers VALUES('Name-1',925,5781725,GETDATE()) INSERT INTO PhoneNumbers VALUES('Name-3',207,3188796,GETDATE()) |
The following query will try to concatenate 4 different data typed expressions. These are VARCHAR, INT, BIGINT, and DATETIME.
Now, we will execute the following query:
|
1 |
SELECT CONCAT(ClientName,AreaCode,PhoneNumber,Dt) AS Result FROM PhoneNumbers |
As we can see, all expressions with different data types are concatenated without any error. Now, we will analyze the execution plan of the query:
When we hover the cursor on the SELECT operator icon, a pop-up window appears and the implicit conversion issue is showing obviously. Implicit conversion is a data conversion operation that is made by SQL Server automatic when it required. In addition, you can look at the Implicit conversion in SQL Server article to learn more details about the implicit conversion notion.
NULL value and CONCAT function in SQL
NULL is a special pointer that identifies the value that is unknown or does not exist in SQL Server. In terms of CONCAT function, if we use a NULL value as a parameter to CONCAT function, it converts the NULL values to an empty string. Now, we will make an example of it:
|
1 |
SELECT CONCAT('Think' , NULL , 'green') AS 'FullString' |
As we can see, the NULL expression used in the CONCAT function did not affect the result of the function and behaved like an empty string. In addition, if we pass all parameters as NULL, the result of the function will be an empty string. Now, we will make an example of this:
|
1 |
SELECT CONCAT(NULL , NULL , NULL) AS 'FullString' |
As a result, we can say that NULL values do not affect the output of the function.
How to use line feed (\n) and carriage return (\r) with CONCAT function
CHAR function enables to convert ASCII numbers to character values. The following ASCII codes can be used to get a new line with CHAR function in SQL:
Value | Char | Description |
10 | LF | Line Feed |
13 | CR | Carriage Return |
We can get a new line when we concatenate the strings with the following CHAR functions:
- CHAR (10): New Line / Line Feed
- CHAR (13): Carriage Return
For example, the following query results in the concatenated string line by line with CHAR(13) function:
|
1 2 |
SELECT CONCAT('Make',CHAR(13),'every' ,CHAR(13),'drop' , CHAR(13) , 'of',CHAR(13),'water',CHAR(13), 'count') AS Result |
Now, we will replace CHAR(13) expression with CHAR(10) expression and re-execute the same query:
|
1 2 |
SELECT CONCAT('Make',CHAR(10),'every' ,CHAR(10),'drop' , CHAR(10) , 'of',CHAR(10),'water',CHAR(10), 'count') AS Result |
At the same time, we can use CHAR(13) and CHAR(10) together. This usage type could be a good option when we want to generate a line break. Now, we will make a demonstration of it:
|
1 2 3 |
SELECT CONCAT('Make',CHAR(10),CHAR(13),'every' ,CHAR(10),CHAR(13),'drop' , CHAR(10),CHAR(13) , 'of',CHAR(10),CHAR(13),'water',CHAR(10),CHAR(13), 'count') AS Result |
Conclusion
In this article, we have learned the CONCAT function in SQL using various examples. CONCAT function is a very useful option to concatenate the expressions in the SQL.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023