求一行以|为间隔的数据按五个一组拆成多行的函数

一行值，以|为间隔的，每五个一组，变成第二行，怎么处理？
如：队列 1|2|张三|王二麻子|5|6|7|8|9|10，
变为
队列 1|2|张三|王二麻子|5|
队列 6|7|8|9|10

9 个解决方案

#1

你这种数据， 是只有一行，还是有多行 ？

#2

引用 1 楼 wmxcn2000 的回复:

你这种数据， 是只有一行，还是有多行 ？

队列一一行；队列二一行；队列三一行；队列四一行。
但是每行的长度不一样，个数不一样，所以要写函数来处理

#3

引用 1 楼 wmxcn2000 的回复:

你这种数据， 是只有一行，还是有多行 ？

队列一的这一行  1|2|张三|王二麻子|5|6|7|8|9|10
转成
队列一  1|2|张三|王二麻子|5|
队列一  6|7|8|9|10

#4

;WITH t(s)AS(
   SELECT N'1|2|张三|王二麻子|5|6|7|8|9|10'
)
,a AS(
    SELECT t.s,n.number  FROM t 
    OUTER APPLY (SELECT sv.number FROM master.dbo.spt_values AS sv 
              WHERE sv.type='P' 
              AND sv.number BETWEEN CHARINDEX('|',t.s) AND LEN(t.s) 
              AND (((sv.number-LEN(REPLACE(LEFT(t.s,sv.number),'|','')))%5=0 AND SUBSTRING(t.s,sv.number,1)='|')
              OR sv.number=LEN(t.s))
              
    )n
)
SELECT a1.*,SUBSTRING(a1.s,ISNULL(b.number,0)+1,a1.number-(ISNULL(b.number,1)-1)) FROM  a AS a1
OUTER APPLY(SELECT TOP 1 a2.number FROM a AS a2 WHERE a2.number<a1.number ORDER BY a2.number desc)  b

/*
s                        number      
------------------------ ----------- ------------------------
1|2|张三|王二麻子|5|6|7|8|9|10 14          1|2|张三|王二麻子|5|
1|2|张三|王二麻子|5|6|7|8|9|10 24          6|7|8|9|10

*/

#5

引用 4 楼 ch21st 的回复:

;WITH t(s)AS(
   SELECT N'1|2|张三|王二麻子|5|6|7|8|9|10'
)
,a AS(
    SELECT t.s,n.number  FROM t 
    OUTER APPLY (SELECT sv.number FROM master.dbo.spt_values AS sv 
              WHERE sv.type='P' 
              AND sv.number BETWEEN CHARINDEX('|',t.s) AND LEN(t.s) 
              AND (((sv.number-LEN(REPLACE(LEFT(t.s,sv.number),'|','')))%5=0 AND SUBSTRING(t.s,sv.number,1)='|')
              OR sv.number=LEN(t.s))
              
    )n
)
SELECT a1.*,SUBSTRING(a1.s,ISNULL(b.number,0)+1,a1.number-(ISNULL(b.number,1)-1)) FROM  a AS a1
OUTER APPLY(SELECT TOP 1 a2.number FROM a AS a2 WHERE a2.number<a1.number ORDER BY a2.number desc)  b

/*
s                        number      
------------------------ ----------- ------------------------
1|2|张三|王二麻子|5|6|7|8|9|10 14          1|2|张三|王二麻子|5|
1|2|张三|王二麻子|5|6|7|8|9|10 24          6|7|8|9|10

*/

看不懂，可能改成函数，不会用sqlserver，只会oracle，谢谢

#6

引用 5 楼 fcydj1984 的回复:

Quote: 引用 4 楼 ch21st 的回复:

;WITH t(s)AS(
   SELECT N'1|2|张三|王二麻子|5|6|7|8|9|10'
)
,a AS(
    SELECT t.s,n.number  FROM t 
    OUTER APPLY (SELECT sv.number FROM master.dbo.spt_values AS sv 
              WHERE sv.type='P' 
              AND sv.number BETWEEN CHARINDEX('|',t.s) AND LEN(t.s) 
              AND (((sv.number-LEN(REPLACE(LEFT(t.s,sv.number),'|','')))%5=0 AND SUBSTRING(t.s,sv.number,1)='|')
              OR sv.number=LEN(t.s))
              
    )n
)
SELECT a1.*,SUBSTRING(a1.s,ISNULL(b.number,0)+1,a1.number-(ISNULL(b.number,1)-1)) FROM  a AS a1
OUTER APPLY(SELECT TOP 1 a2.number FROM a AS a2 WHERE a2.number<a1.number ORDER BY a2.number desc)  b

/*
s                        number      
------------------------ ----------- ------------------------
1|2|张三|王二麻子|5|6|7|8|9|10 14          1|2|张三|王二麻子|5|
1|2|张三|王二麻子|5|6|7|8|9|10 24          6|7|8|9|10

*/

看不懂，可能改成函数，不会用sqlserver，只会oracle，谢谢

上面大致思路是从左到右扫描字符串，将字符中|替换没，和替换前长度的差就是包含的|个数
从第一个开始，每到 | 出现5的倍数就列出
然后在上面结果的基础上，用substring取当字符上一行位置到当前行位置就是本行对应的子字符串

我没有oracle环境，没法测试
你也可以将字符串先拆分，在5个一组进行连接（mysql 有group_concat)
下面是MSSQL的拆分，Oracle有自己的拆分，如regexp_substr

;WITH t(s)AS(
   SELECT N'1|2|张三|王二麻子|5|6|7|8|9|10'
)

SELECT t1.s,t2.* FROM (
   SELECT *,CONVERT(XML,'<n>'+REPLACE(t.s,'|','</n><n>')+'</n>') AS x FROM t
) t1
CROSS APPLY (SELECT (ROW_NUMBER()OVER(PARTITION BY t1.s ORDER BY GETDATE())-1)/5 AS groupid, s.b.value('.','nvarchar(100)') AS c FROM t1.x.nodes('n')s(b)) t2

/*
s                                              groupid                  c
------------------------                   -------------------- ----------------------------------------------------------------------------------------------------
1|2|张三|王二麻子|5|6|7|8|9|10 0                    1
1|2|张三|王二麻子|5|6|7|8|9|10 0                    2
1|2|张三|王二麻子|5|6|7|8|9|10 0                    张三
1|2|张三|王二麻子|5|6|7|8|9|10 0                    王二麻子
1|2|张三|王二麻子|5|6|7|8|9|10 0                    5
1|2|张三|王二麻子|5|6|7|8|9|10 1                    6
1|2|张三|王二麻子|5|6|7|8|9|10 1                    7
1|2|张三|王二麻子|5|6|7|8|9|10 1                    8
1|2|张三|王二麻子|5|6|7|8|9|10 1                    9
1|2|张三|王二麻子|5|6|7|8|9|10 1                    10
*/

#7

SQL SERVER函数分拆
e.g.

CREATE FUNCTION fn_SplitTab ( @Str VARCHAR(MAX) )
RETURNS TABLE
AS
RETURN
    ( SELECT    STUFF(a.Col.query('c[position()>sql:column("b.SNum") and position()<=sql:column("b.ENum")]').value('.',
                                                              'varchar(50)'),
                      1, 1, '') AS Col
      FROM      ( SELECT    CONVERT(XML, '<c>|' + REPLACE(@Str, '|',
                                                          '</c><c>|') + '</c>') AS Col
                ) AS a
                CROSS JOIN ( SELECT b.number * 5 AS SNum ,
                                    ( b.number + 1 ) * 5 AS ENum
                             FROM   master.dbo.spt_values AS b
                             WHERE  b.type = 'P'
                                    AND b.number < CEILING(( LEN(@Str)
                                                             - LEN(REPLACE(@Str,
                                                              '|', '')) )
                                                           / 5.0)
                           ) AS b
    );

GO
SELECT * FROM fn_SplitTab(N'1|2|张三|王二麻子|5|6|7|8|9|10')
/*
1|2|张三|王二麻子|5
6|7|8|9|10
*/

#8

引用 7 楼 roy_88 的回复:

SQL SERVER函数分拆
e.g.

CREATE FUNCTION fn_SplitTab ( @Str VARCHAR(MAX) )
RETURNS TABLE
AS
RETURN
    ( SELECT    STUFF(a.Col.query('c[position()>sql:column("b.SNum") and position()<=sql:column("b.ENum")]').value('.',
                                                              'varchar(50)'),
                      1, 1, '') AS Col
      FROM      ( SELECT    CONVERT(XML, '<c>|' + REPLACE(@Str, '|',
                                                          '</c><c>|') + '</c>') AS Col
                ) AS a
                CROSS JOIN ( SELECT b.number * 5 AS SNum ,
                                    ( b.number + 1 ) * 5 AS ENum
                             FROM   master.dbo.spt_values AS b
                             WHERE  b.type = 'P'
                                    AND b.number < CEILING(( LEN(@Str)
                                                             - LEN(REPLACE(@Str,
                                                              '|', '')) )
                                                           / 5.0)
                           ) AS b
    );

GO
SELECT * FROM fn_SplitTab(N'1|2|张三|王二麻子|5|6|7|8|9|10')
/*
1|2|张三|王二麻子|5
6|7|8|9|10
*/

SELECT * FROM fn_SplitTab(N'1|2|张三|王二麻子|5|6|7|8|9|10'执行不了，函数里有问题

#9

引用 8 楼 fcydj1984 的回复:

SELECT * FROM fn_SplitTab(N'1|2|张三|王二麻子|5|6|7|8|9|10'执行不了，函数里有问题

SQL 什么版本？SQL 05以上版可用此方法