在SQL Server数据库中,我们可能会因为工作的需要,必须拼接字符串。好多朋友喜欢这样拼接字符串(SQL Server 2008 R2,CTE部分是伪造的示例数据):
- WITH TEMP AS
- (
- SELECT 'A' AS ITEM
- UNION ALL
- SELECT 'B' AS ITEM
- UNION ALL
- SELECT 'C' AS ITEM
- )
- SELECT A.ITEM+',' AS [data()]
- FROM TEMP A
- FOR XML PATH('')
这个QUERY会得到这样的XML:
A, B, C,
但是潜在的问题是当数据不是字符型的时候会出问题,试把CTE中的数据全部改为数字。
- WITH TEMP AS
- (
- SELECT 1 AS ITEM
- UNION ALL
- SELECT 2 AS ITEM
- UNION ALL
- SELECT 3 AS ITEM
- )
- SELECT A.ITEM+',' AS [data()]
- FROM TEMP A
- FOR XML PATH('')
这个QUERY会报错:
Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the varchar value ',' to data type int.
所以,我推荐的拼接方式是:
- WITH TEMP AS
- (
- SELECT 1 AS ITEM
- UNION ALL
- SELECT 2 AS ITEM
- UNION ALL
- SELECT 3 AS ITEM
- )
- SELECT A.ITEM AS [data()],','
- FROM TEMP A
- FOR XML PATH('')
运行结果是:
1,2,3,
关于SQL Server数据库拼接字符串的问题就介绍这么多,如果要了解更多SQL Server数据库的问题,可以到这里来:http://database.51cto.com/sqlserver/,谢谢大家的支持!
【编辑推荐】