在开发中,很多时候都需要处理拆分字符串的操作。下面收集了几种方法供大家分享,其中的逗号可以改为多种有需要的符号,但是不能针对多种符号同时存在的例子。有待各位补充:
说明:
将字符串转换为正规化的数据表,可以使用多种方法实现,比如前端程序处理、游标、数据库循环函数,都是常用的技巧。
对于处理这些问题,比较好的思路就是使用SUBSTRING函数取出字符串,然后使用CHARINDEX函数定位。最后搭配排序函数完成拆分。
对于2005以后,可以使用CTE来实现。另外可以自定义一个函数处理。搭配输入分隔符与字符串,然后以TABLE方式返回。
解决方法:
下面是这4种方式的示例:
备注:首先针对原始字符串,可以使用BULKINSERT 后者BCP命令甚至直接INSERT语句,把原始数据导入数据表。以下语句是待处理的数据产生脚本,有业务代号和业务员订单:
为了输出每个业务员的每笔订单序号,所以用循环产生一个数据表,储存指定数量的序号。
接下来就是上面提到的4种方式的实现:
USE tempdb
GO
--建立数据表存储原始数据:
CREATE TABLE
Arrays
(
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 TABLE
Nums
(
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 salesID
ORDER BY
c1 ) AS
[序号]
,
SUBSTRING(salesOrd,
c1,
CHARINDEX(', ',
salesOrd +
', ', c1)
- c1)
AS [值]
FROM
Arrays
JOIN
Nums ON c1
<= LEN(salesOrd)
ANDSUBSTRING(', '
+ salesOrd,
c1, 1)
= ', '
ORDER BY
salesID ,
[序号]
--方法二:适用于任何版本
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
FROMdbo.Arrays
WHERELEN(salesOrd)
> 0
UNION
ALL
SELECTPrv.salesID
,
Prv.pos+ 1
,
Prv.endpos+ 2
,
CHARINDEX(', ',
CUR.salesOrd
+ ', ',
Prv.endpos
+ 2)
- 1
FROMSplitCTE
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方法的实现开销最小,所以建议使用这种方式处理:
分享到:
相关推荐
Transact-SQL元编程——用T-SQL生成T-SQL.pdf
T-SQL核对清单——13条卓有成效的T-SQL实践.pdf
T-SQL性能调优秘籍——基于SQL Server 2012窗口函数,书中提及的网站已经打不开了,在github上找到了。路径在附件中。
文件列表 Chapter 01 - SQL Windowing.sql Chapter 02 - A Detailed Look at Window Functions.sql Chapter 03 - Ordered Set Functions.sql ...Chapter 05 - T-SQL Solutions using Window Functions.sql TSQL2012.sql
1、处理常见业务问题,如总计、间隔、...3、T-SQL性能调优秘笈:基于SQL Server 2012窗口函数》基于SQLServer2012,讨论了SQL窗口、窗口函数、排序集合函数、窗口函数的优化以及利用窗口函数的T-SQL解决方案等内容。
MS-SQL开发常用汇总和T-SQL技巧集锦 MS-SQL开发常用汇总和T-SQL技巧集锦 MS-SQL开发常用汇总和T-SQL技巧集锦 详尽的数据库常用开发和技巧集锦
整理了一些t-sql技巧
MS-SQL开发常用汇总和t-sql技巧集锦MS-SQL开发常用汇总和t-sql技巧集锦
本书及其续篇——《Microsoft SQL Server 2005技术内幕:T-SQL程序设计》介绍了SQL Server 2005中高级T-SQL查询、查询优化及编程相关的知识。这两本书侧重于解决实践中的常见问题,并讨论了解决这些问题的方法。它们...
本书及其续篇——《Microsoft SQL Server 2005技术内幕:T-SQL程序设计》介绍了SQL Server 2005中高级T-SQL查询、查询优化及编程相关的知识。这两本书侧重于解决实践中的常见问题,并讨论了解决这些问题的方法。它们...
在开发中,很多时候都需要处理拆分字符串的操作。下面收集了几种方法供大家分享,其中的逗号可以改为多种有需要的符号,但是不能针对多种符号同时存在的例子。有待各位补充: 说明: 将字符串转换为正规化的...
☆ 资源说明:☆ [Apress] SQL Server 2012 T-SQL 实用技巧 (英文版) [Apress] SQL Server 2012 T-SQL Recipes (E-Book)
该书解释并比较了SQL Server 2000和SQL Server 2005在数据库开发相关问题上的解决方案,深入讨论了SQL Server 2005中新增的T-SQL编程特性,包含了大量的代码示例、表示例和逻辑难题以帮助数据库开发人员和管理员理解...
创建函数,拆分字符串
SQL Server 2012 T-SQL 实用技巧 (英文版)
数据库原理及应用实验指导★---实验5-SQL语言——更新操作命令(含部分解答).pdf
里面有实验文档、sql、详细的代码,都是自己写的,所以比较简单。 可供参考
SQL Server 技术内幕之T-SQL编程原版CHM SQL Server 2005 技术内幕四本中的其一 本书是Inside Microsoft SQL Server 2005系列四本著作中的一本。它详细介绍了T-SQL的内部构造,包含了非常全面的编程参考。它提供了...
工作中常用的T-SQL语句,超详细。内含解释说明,使用技巧和运用规则,方便开发时随时查阅。