T-SQL技巧收集——拆分字符串
来源:互联网 发布:mac 水漾润泽 编辑:程序博客网 时间:2024/05/17 02:22
在开发中,很多时候都需要处理拆分字符串的操作。下面收集了几种方法供大家分享,其中的逗号可以改为多种有需要的符号,但是不能针对多种符号同时存在的例子。有待各位补充:
说明:
将字符串转换为正规化的数据表,可以使用多种方法实现,比如前端程序处理、游标、数据库循环函数,都是常用的技巧。
对于处理这些问题,比较好的思路就是使用SUBSTRING函数取出字符串,然后使用CHARINDEX函数定位。最后搭配排序函数完成拆分。
对于2005以后,可以使用CTE来实现。另外可以自定义一个函数处理。搭配输入分隔符与字符串,然后以TABLE方式返回。
解决方法:
下面是这4种方式的示例:
备注:首先针对原始字符串,可以使用BULKINSERT 后者BCP命令甚至直接INSERT语句,把原始数据导入数据表。以下语句是待处理的数据产生脚本,有业务代号和业务员订单:
为了输出每个业务员的每笔订单序号,所以用循环产生一个数据表,储存指定数量的序号。
接下来就是上面提到的4种方式的实现:
USE tempdb
GO
--建立数据表存储原始数据:
CREATE TABLEArrays
(
salesID VARCHAR(10)NOT NULL ,
salesOrd VARCHAR(8000)NOT NULL
)
GO
--注意:下面insert语句中逗号后面有一个空格
INSERT INTO Arrays
VALUES ('A', '20, 223, 2544' ) ;
INSERT INTO Arrays
VALUES ('B', '30, 23433, 28' ) ;
INSERT INTO Arrays
VALUES ('C', '12, 10' ) ;
INSERT INTO Arrays
VALUES ('D', '4, 6, 45678, 2' ) ;
GO
--通过循环产生存储指定数量的序号的表
CREATE TABLENums
(
c1 INT NOT NULL
PRIMARY KEY
) ;
GO
--产生数据
DECLARE @i INT
SET @i= 1
WHILE @i<= 8000
BEGIN
INSERT INTO Nums
VALUES (@i )
SET @i = @i + 1
END
--方法一:适合2005以上版本使用。使用ROW_NUBMER函数
SELECT salesID [业务编号],
ROW_NUMBER()OVER ( PARTITION BY salesIDORDER BY c1 ) AS [序号] ,
SUBSTRING(salesOrd,c1, CHARINDEX(', ',salesOrd + ', ', c1)- c1)AS [值]
FROM Arrays
JOIN Nums ON c1<= LEN(salesOrd)
ANDSUBSTRING(', '+ salesOrd,c1, 1)= ', '
ORDER BYsalesID ,
[序号]
--方法二:适用于任何版本
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 BYsalesID ,
[序号]
--方式三:适用于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 SplitCTEASPrv
JOIN dbo.ArraysAS Cur ON CUR.salesID= Prv.salesID
ANDCHARINDEX(', ',
cur.salesOrd
+', ',
Prv.endpos+ 2) > 0
)
SELECT A.salesID AS [业务编号],
pos [序号] ,
CAST(SUBSTRING(salesOrd,startpos, endpos - startpos+ 1) AS INT) AS [值]
FROM dbo.ArraysAS a
JOIN SplitCTEAS SON S.salesID= A.salesID
ORDER BY A.salesID,
pos
GO
--方法4:使用自定义函数
CREATE FUNCTIONdbo.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方法的实现开销最小,所以建议使用这种方式处理:
- T-SQL技巧收集——拆分字符串
- 数据库技巧汇总——拆分字符串
- T-SQL性能调整——信息收集
- T-SQL技术收集——删除重复数据
- T-SQL技术收集——删除重复数据
- T-SQL技术收集——删除重复数据
- T-SQL技术收集——删除重复数据
- T-SQL技术收集——删除重复数据
- SQL拆分字符串
- 利用sql拆分字符串
- SQL 字符串拆分
- sql拆分字符串
- SQL 字符串拆分例子
- SQL 拆分参数字符串
- sql server拆分字符串
- sql拆分字符串
- sql拆分字符串
- sql 拆分字符串 Split
- QT事件
- 创建事件过滤器和安装事件过滤器
- sencha中launch以及之前的活动
- 腾讯由IT 走向CT
- SearchChar
- T-SQL技巧收集——拆分字符串
- v310 拍照发现预览照片时显示异常
- c++ vector用法精讲
- Google I/O 2012即将开幕 众多亮点先睹为快
- java的远程方法调用
- XCode的一些调试技巧
- 儿子让我在坚强中求生
- WINCE系统中coredll.dll有什么用?
- java.lang.LinkageError: JAXB 2.0 API is being loaded from the bootstrap classloader, but this RI(xxx