sql中concat函数_SQL中的CONCAT函数概述和示例

sql中concat函数

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

存储非Unicode字符数据的字符串数据类型

 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
 字符串数据类型 数据类型 最低限度 最高限额 烧焦 0个字符 8000个字符 varchar 0个字符 8000个字符 varchar（最大值） 0个字符 2 ^ 31个字符 文本 0个字符 2,147,483,647字符
• Unicode character strings data types that store Unicode character data

存储Unicode字符数据的Unicode字符串数据类型

 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
 Unicode字符串数据类型 数据类型 最低限度 最高限额 nchar 0个字符 4000个字符 nvarchar 0个字符 4000个字符 文字 0个字符 1,073,741,823字符

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.

SQL字符串函数用于操纵字符表达式或获取有关它们的各种信息。 SQL中的CONCAT函数是这些函数中最有用的成员之一。 CONCAT函数是一个SQL字符串函数，可将两个或两个以上的字符表达式连接成一个字符串。 现在，我们将通过一个简单的例子来说明这一点。

CONCAT函数语法 (CONCAT function syntax)

The syntax of the function looks like as follows:

CONCAT ( string_value1, string_value2 [, string_valueN ] )

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函数至少需要两个参数，并且该函数最多可以接受254个参数。

CONCAT功能示例 (CONCAT function examples)

In this example, we will join Think and green strings with the CONCAT function:

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:

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:

DECLARE @Str1 AS VARCHAR(100)='Think'
DECLARE @Str2 AS VARCHAR(100)='-'
DECLARE @Str3 AS VARCHAR(100)='green'

SELECT CONCAT(@Str1,@Str2,@Str3) AS ResultString


在SQL中用CONCAT函数连接数值表达式 (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:

CONCAT函数还具有连接数值的功能。 在下面的示例中，我们将连接三个不同的整数值：

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:

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:

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:

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:

INSERT INTO Test_NumericValue VALUES (11,33,99)


Now, we will execute the below SELECT statement in the ApexSQL Plan:

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:

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:

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.

SQL中的NULL值和CONCAT函数 (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:

NULL是一个特殊的指针，用于标识SQL Server中未知或不存在的值。 就CONCAT函数而言，如果我们将NULL值用作CONCAT函数的参数，则它将NULL值转换为空字符串。 现在，我们将举一个例子：

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:

SELECT CONCAT(NULL , NULL , NULL) AS 'FullString'


As a result, we can say that NULL values do not affect the output of the function.

如何在CONCAT函数中使用换行（\ n）和回车（\ r） (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
 值 烧焦 描述 10 如果 换行 13 CR 回车

We can get a new line when we concatenate the strings with the following CHAR functions:

• CHAR (10): New Line / Line Feed CHAR（10）：新行/换行
• CHAR (13): Carriage Return CHAR（13）：回车

For example, the following query results in the concatenated string line by line with CHAR(13) function:

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:

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:

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.

sql中concat函数

• 2
点赞
• 0
评论
• 4
收藏
• 一键三连
• 扫一扫，分享海报

08-14

07-09 2315
03-22 3579
08-10 8370
06-12 8万+
08-14 1万+
03-08 1万+
04-23 3252
11-14 6646
05-08 403
12-20 5276
12-12 1018
08-01 10万+
04-28 157
06-22 1347