SQLServer实现split分割字符串到列

为什么80%的码农都做不了架构师？>>>   

网上已有人实现sqlserver的split函数可将字符串分割成行，但是我们习惯了split返回数组或者列表，因此这里对其做一些改动，最终实现也许不尽如意，但是也能解决一些问题。

先贴上某大牛写的split函数（来自：Split function in SQL Server to break Comma separated strings，注意我这里将其命名为splitl）：

ALTER 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

其原理还是比较简单的，一看便知。调用该函数返回的结果是：

SELECT * FROM dbo.splitl('a#b#c#d','#')

然而我希望得到的结果是：

SELECT 'a' a,'b' b,'c' c,'d' d

这就要用到sqlserver行转列的技巧，网上有很多方法可以参照。下面真正的split“过程”来了：

ALTER 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语法，参见：使用 PIVOT 和 UNPIVOT

注意这个过程调用了splitl函数，是在其基础上开发的。我们再来看看执行结果：

EXEC dbo.split 'a#b#c#d','#'

发现与上面期望的效果完全一致了！

但是这只是针对一行数据做split，如果是查询结果有多行都要分割怎么办呢？

我没有找到办法，因为sqlserver查询语句中不能嵌套过程，只能调用函数，而函数返回的结果集不能是多行。

but..世上无难事，只要写过程：

-- 删除结果表
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),
	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毕竟不够完美，这样的函数系统提供能够最好，自己实现的话遇到太多瓶颈，比如函数不支持动态语句，不能将查询结果传入过程等等。

至于实际应用，将上面这个栗子建立临时数据表的部分替换成要查询的真实表列即可，最后结果如下所示：

转载于:https://my.oschina.net/cwalet/blog/267386