SQL Server拼接技巧大全

1.字符串拼接的基本操作

在SQL Server中，字符串拼接是一种常见的操作。通常使用"+"运算符或"concat"函数实现字符串拼接。

-- 使用"+"运算符拼接字符串

DECLARE @str1 VARCHAR(20)='Hello'
DECLARE @str2 VARCHAR(20)='World'
SET @str1 = @str1 + ' ' + @str2
PRINT @str1
-- 使用"concat"函数拼接字符串
DECLARE @str3 VARCHAR(20)='Hello'
DECLARE @str4 VARCHAR(20)='World'
SET @str3 = CONCAT(@str3,' ',@str4)
PRINT @str3

在以上示例中，我们分别使用"+"运算符和"concat"函数拼接字符串，结果都是"Hello World"。

1.1 使用FOR XML PATH拼接字符串

除了上述方法，还可以使用FOR XML PATH进行字符串拼接。它可以将每一行数据拼接成一个字符串，更适合在SQL Server中进行拼接。

-- 使用FOR XML PATH拼接字符串

SELECT STUFF((SELECT ','+name FROM sys.sysobjects FOR XML PATH('')),1,1,'') AS names

在以上示例中，我们使用FOR XML PATH拼接sys.sysobjects表中的所有对象名，以逗号分隔。STUFF函数用于去除第一个逗号，将结果作为"names"列返回。

1.2 使用STRING_AGG函数拼接字符串

SQL Server 2017及以上版本支持STRING_AGG函数，它可以更方便地实现字符串拼接。STRING_AGG函数可以将多行数据按指定分隔符进行拼接，并且可以指定排序方式。

-- 使用STRING_AGG函数拼接字符串

SELECT STRING_AGG(name,',') AS names FROM sys.sysobjects

在以上示例中，我们使用STRING_AGG函数拼接sys.sysobjects表中的所有对象名，以逗号分隔。

2.字符串拼接技巧

2.1 使用CASE语句拼接字符串

在实际应用中，我们可能需要根据不同的条件拼接字符串，这时可以使用CASE语句实现。

-- 使用CASE语句拼接字符串

SELECT name, 
       CASE WHEN type='U' THEN '表' 
            WHEN type='V' THEN '视图' 
            WHEN type='P' THEN '存储过程'
            ELSE '未知类型' END AS type_desc
FROM sys.objects

在以上示例中，我们使用CASE语句根据对象的类型返回不同的类型描述，最终以"type_desc"列返回。

2.2 使用XML PATH拼接不定数量的参数

在一些场景下，我们需要拼接不定数量的参数。此时，可以将参数转换成XML格式，使用XML PATH进行拼接。

-- 使用XML PATH拼接不定数量的参数

CREATE FUNCTION fn_concat_string
(
    @delimiter VARCHAR(10), -- 分隔符
    @params VARCHAR(MAX) -- 参数字符串，以逗号分隔
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @xml XML
    SET @xml = ''+REPLACE(@params,',','')+''
    RETURN STUFF((SELECT @delimiter+x.value('.','VARCHAR(MAX)') FROM @xml.nodes('x') AS T(x) FOR XML PATH('')),1,LEN(@delimiter),'')
END
-- 使用函数拼接字符串
SELECT dbo.fn_concat_string(',','1,2,3,4,5') AS result

在以上示例中，我们创建了一个名为"fn_concat_string"的函数，可以使用它来拼接不定数量的参数。函数将参数转换成XML格式，使用XML PATH拼接字符串，并允许指定分隔符。

2.3 使用变量拼接动态SQL语句

动态SQL是指在SQL语句中使用变量表示表名、列名等信息，以方便实现可重用的通用SQL代码。在实际应用中，我们可能需要拼接动态SQL语句，以便根据不同的条件生成不同的SQL语句。

-- 使用变量拼接动态SQL语句

DECLARE @table_name VARCHAR(50)='employee'
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT * FROM '+@table_name+' WHERE gender=''M'''
EXEC(@sql)

在以上示例中，我们使用变量@table_name表示表名，在动态SQL语句中拼接生成完整SQL语句，并执行。

2.4 使用JSON拼接数据

在SQL Server 2016及以上版本中，可以使用JSON组合拼接数据。JSON可以表示复杂的数据结构，并提供了方便的拼接和解析API。

-- 使用FOR JSON拼接JSON数据

SELECT id, name, gender FROM employee FOR JSON AUTO

在以上示例中，我们使用FOR JSON将employee表中的数据以JSON格式返回。

3.总结

本文介绍了SQL Server中常见的字符串拼接方法，并提供了一些实用的技巧，如使用XML PATH和JSON拼接数据，使用变量拼接动态SQL等。通过掌握这些技巧，可以更方便地实现字符串拼接操作，提高开发效率。