SQLServer实现split分割字符串到列

Easter79
• 阅读 526

网上已有人实现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','#')

SQLServer实现split分割字符串到列

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

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

SQLServer实现split分割字符串到列

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

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

SQLServer实现split分割字符串到列

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Wesley13 Wesley13
2年前
java将前端的json数组字符串转换为列表
记录下在前端通过ajax提交了一个json数组的字符串,在后端如何转换为列表。前端数据转化与请求varcontracts{id:'1',name:'yanggb合同1'},{id:'2',name:'yanggb合同2'},{id:'3',name:'yang
Bill78 Bill78
3年前
python中的split()函数的用法
函数:split()Python中有split()和os.path.split()两个函数,具体作用如下:split():拆分字符串。通过指定分隔符对字符串进行切片,并返回分割后的字符串列表(list)os.path.split():按照路径将文件名和路径分割开一、函数说明1、split()函数语法:str.split(str
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Python split() 函数 拆分字符串 将字符串转化为列
函数:split()Python中有split()和os.path.split()两个函数,具体作用如下:split():拆分字符串。通过指定分隔符对字符串进行切片,并返回分割后的字符串列表(list)os.path.split():按照路径将文件名和路径分割开一、函数说明1、split()函数语法:str.split(str"",numstring.c
Stella981 Stella981
2年前
JavaScript 常用数组函数方法专题
1、由字符串生成数组split() 分割字符串,并将分割的部分作为一个元素保存在一个新建的数组中。varstr1"thisisanemampletousingthemethodofarray.";varstr2str1.split("");//以空格作为分割条件
Stella981 Stella981
2年前
SQLServer实现split分割字符串到列
网上已有人实现sqlserver的split函数可将字符串分割成行,但是我们习惯了split返回数组或者列表,因此这里对其做一些改动,最终实现也许不尽如意,但是也能解决一些问题。先贴上某大牛写的split函数(来自:SplitfunctioninSQLServertobreakCommaseparatedstrings(https:
Stella981 Stella981
2年前
JS 对象数组Array 根据对象object key的值排序sort,很风骚哦
有个js对象数组varary\{id:1,name:"b"},{id:2,name:"b"}\需求是根据name或者id的值来排序,这里有个风骚的函数函数定义:function keysrt(key,desc) {  return function(a,b){    return desc ? ~~(ak
Stella981 Stella981
2年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Python进阶者 Python进阶者
4个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这
Easter79
Easter79
Lv1
今生可爱与温柔,每一样都不能少。
文章
2.8k
粉丝
5
获赞
1.2k