T-SQL技巧收集??拆分字符串
作者:网络转载 发布时间:[ 2013/1/23 10:03:00 ] 推荐标签:
--方法二:适用于任何版本
SELECT salesID [业务编号] ,
c1 - LEN(REPLACE(LEFT(salesOrd, c1), ', ', '')) + 1 [序号] ,
SUBSTRING(salesOrd, c1, CHARINDEX(', ', salesOrd + ', ', c1) - c1) AS [值]
FROM arrays
JOIN Nums ON c1 <= LEN(salesOrd)
ANDSUBSTRING(', ' + salesOrd, c1, 1) = ', '
ORDER BY salesID ,
[序号]
--方式三:适用于2005以上版本,使用CTE实现:
WITH SplitCTE
AS ( SELECT salesID ,
1 AS pos ,
1 AS startpos ,
CHARINDEX(', ', salesOrd + ', ') - 1 AS endpos
FROM dbo.Arrays
WHERE LEN(salesOrd) > 0
UNION ALL
SELECT Prv.salesID ,
Prv.pos+ 1 ,
Prv.endpos+ 2 ,
CHARINDEX(', ', CUR.salesOrd + ', ', Prv.endpos + 2)
- 1
FROM SplitCTE ASPrv
JOIN dbo.Arrays AS Cur ON CUR.salesID = Prv.salesID
AND CHARINDEX(', ',
cur.salesOrd
+ ', ',
Prv.endpos + 2) > 0
)
SELECT A.salesID AS [业务编号] ,
pos [序号] ,
CAST(SUBSTRING(salesOrd, startpos, endpos - startpos + 1) AS INT) AS [值]
FROM dbo.Arrays AS a
JOIN SplitCTEAS S ON S.salesID = A.salesID
ORDER BY A.salesID ,
pos
GO
--方法4:使用自定义函数
CREATE FUNCTION dbo.fn_split( @orders AS VARCHAR(MAX) )
RETURNS TABLE
AS
RETURN
SELECT c1 - LEN(REPLACE(LEFT(@orders, c1), ', ', '')) + 1 AS [序号] ,
SUBSTRING(@orders, c1, CHARINDEX(', ', @orders + ', ', c1) - c1) AS [值]
FROM dbo.Nums
WHERE c1 <= LEN(@orders)
AND SUBSTRING(', ' + @orders, c1, 1) = ', ' ;
GO
--然后使用cross apply技巧,合并分解字符串
SELECT salesID ,
B.*
FROM Arrays a
CROSS APPLY dbo.fn_split(a.salesOrd) b
GO
通过一下执行计划的开销可以看到CTE方法的实现开销小,所以建议使用这种方式处理:
相关推荐
更新发布
功能测试和接口测试的区别
2023/3/23 14:23:39如何写好测试用例文档
2023/3/22 16:17:39常用的选择回归测试的方式有哪些?
2022/6/14 16:14:27测试流程中需要重点把关几个过程?
2021/10/18 15:37:44性能测试的七种方法
2021/9/17 15:19:29全链路压测优化思路
2021/9/14 15:42:25性能测试流程浅谈
2021/5/28 17:25:47常见的APP性能测试指标
2021/5/8 17:01:11