SQLServer实现split分割字符串到列
⽹上已有⼈实现sqlserver的split函数可将字符串分割成⾏，但是我们习惯了split返回数组或者列表，因此这⾥对其做⼀些改动，最终实现也许不尽如意，但是也能解决⼀些问题。
先贴上某⼤⽜写的split函数（来⾃：，注意我这⾥将其命名为splitl）：
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24ALTER FUNCTION dbo.splitl (
@String VARCHAR(MAX),
@Delimiter VARCHAR(MAX)
) RETURNS@temptable TABLE(items VARCHAR(MAX)) AS BEGIN
字符串转数组怎么转
DECLARE@idx INT=1
DECLARE@slice VARCHAR(MAX)
IF LEN(@String) < 1 OR LEN(ISNULL(@String,'')) = 0
RETURN
WHILE @idx != 0
BEGIN
SET@idx = CHARINDEX(@Delimiter,@String)
IF @idx != 0
SET@slice = LEFT(@String,@idx - 1)
ELSE
SET@slice = @String
IF LEN(@slice) > 0
INSERT INTO@temptable(items) VALUES(@slice)        SET@String = RIGHT(@String, LEN(@String) - @idx)        IF LEN(@String) = 0
BREAK
END
RETURN
END
其原理还是⽐较简单的，⼀看便知。调⽤该函数返回的结果是：
1SELECT* FROM dbo.splitl('a#b#c#d','#')
然⽽我希望得到的结果是：
1SELECT'a'a,'b'b,'c'c,'d'd
这就要⽤到sqlserver⾏转列的技巧，⽹上有很多⽅法可以参照。下⾯真正的split“过程”来了：
1 2 3 4 5 6 7ALTER PROC [dbo].[split] @strs VARCHAR(MAX),@delimiter VARCHAR(MAX) AS
SELECT items,id=IDENTITY(INT,1,1) INTO#ccc FROM dbo.splitl(@strs,@delimiter)
DECLARE@str VARCHAR(MAX)='',@SQL VARCHAR(MAX)=''
SELECT@str = @str + ','+ '['+ CONVERT(VARCHAR(MAX),id) + ']'FROM#ccc
SET@SQL = 'SELECT * FROM #ccc PIVOT(MAX(items) FOR id IN('+ SUBSTRING(@str,2,LEN(@str)) + ')) b' EXEC(@SQL)
DROP TABLE#ccc
该过程中使⽤了pivot语法，参见：
注意这个过程调⽤了splitl函数，是在其基础上开发的。我们再来看看执⾏结果：
1EXEC dbo.split 'a#b#c#d','#'
发现与上⾯期望的效果完全⼀致了！
但是这只是针对⼀⾏数据做split，如果是查询结果有多⾏都要分割怎么办呢？
我没有到办法，因为sqlserver查询语句中不能嵌套过程，只能调⽤函数，⽽函数返回的结果集不能是多⾏。but..世上⽆难事，只要写过程：
1
2 3 4 5 6 7-- 删除结果表
IF EXISTS (SELECT* FROM dbo.sysobjects WHERE id=object_id(N'test_result') AND OBJECTPROPERTY(id, N'IsUserTable')=1)    DROP TABLE test_result
-- 建⽴数据表
CREATE TABLE#tmp (
id INT NOT NULL IDENTITY(0,1),
7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40    id INT NOT NULL IDENTITY(0,1),
str VARCHAR(MAX)
)
INSERT INTO#tmp SELECT'a#b#c#d'UNION SELECT'f#g#h'
-- ⽣成结果表
DECLARE@maxc INT=(SELECT MAX(LEN(str)-LEN(REPLACE(str,'#','')))+1 FROM#tmp) DECLARE@sql0 VARCHAR(MAX)='CREATE TABLE test_result ('
DECLARE@x INT=0
WHILE @x<@maxc BEGIN
SET@sql0 = @sql0 + 'a'+ CONVERT(VARCHAR(MAX),@x) + ' VARCHAR(MAX),'
SET@x=@x+1
END
SET@sql0 = SUBSTRING(@sql0,0,LEN(@sql0)) + ')'
EXEC(@sql0)
-- 遍历数据表
DECLARE@i INT=0
WHILE @i<(SELECT COUNT(1) FROM#tmp) BEGIN
DECLARE@strs VARCHAR(MAX)=(SELECT str FROM#tmp WHERE id=@i)
DECLARE@cols INT=(SELECT LEN(@strs)-LEN(REPLACE(@strs,'#','')))+1
DECLARE@y INT=0
DECLARE@sql1 VARCHAR(MAX)='INSERT INTO test_result('
WHILE @y<@cols BEGIN
SET@sql1 = @sql1 + 'a'+ CONVERT(VARCHAR(MAX),@y) + ','
SET@y=@y+1
END
-- -- 分割字符串
SET@sql1 = SUBSTRING(@sql1,0,LEN(@sql1)) + ') EXEC split "'+ @strs + '","#"'
EXEC(@sql1)
SET@i=@i+1
END
SELECT* FROM test_result
暂时就到此为⽌⼋~sqlserver毕竟不够完美，这样的函数系统提供能够最好，⾃⼰实现的话遇到太多瓶颈，⽐如函数不⽀持动态语句，不能将查询结果传⼊过程等等。⾄于实际应⽤，将上⾯这个栗⼦建⽴临时数据表的部分替换成要查询的真实表列即可，最后结果如下所⽰：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。