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

一行值，以|为间隔的，每五个一组，变成第二行，怎么处理？
如：队列 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以上版可用此方法

#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，谢谢



;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以上版可用此方法

秒客网

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

9 个解决方案

#1

#2

#3

#4

#5

#6

#7

#8

#9

#1

#2

#3

#4

#5

#6

#7

#8

#9

相关文章