拆字符串
MC
000010000600002
000010000600007
000010000700005
000010000700006
000020000500007
想要拆成下面的结果：
MC
00001
0000100006
000010000600002
000010000600007
0000100007
000010000700005
000010000700006
00002
0000200005
000020000500007
求简单的实现方式，最好是一条SQL语句！！
在此感谢各位！！
[解决办法]
--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([MC] VARCHAR(15))
INSERT #tb
SELECT '000010000600002' UNION ALL
SELECT '000010000600007' UNION ALL
SELECT '000010000700005' UNION ALL
SELECT '000010000700006' UNION ALL
SELECT '000020000500007'
--------------开始查询--------------------------
SELECT DISTINCT [MC]=SUBSTRING(MC,1,number*5)
FROM #tb t,master..spt_values s WHERE s.type='P' AND number BETWEEN 1 AND 10
----------------结果----------------------------
/*
MC
00001
0000100006
000010000600002
000010000600007
0000100007
000010000700005
000010000700006
00002
0000200005
000020000500007
*/
declare @tb table(id int identity(1,1),MC varchar(50))
insert into @tb
select '000010000600002' union all
select '000010000600007' union all
select '000010000700005' union all
select '000010000700006' union all
select '000020000500007' union all
select '00001000020000300004000050000600007000080000900010'
;with maco as
(
select distinct id,b.number from @tb a
left join master..spt_values b on CEILING(LEN(a.MC)/5.0)>=b.number
where b.type='p' and CEILING(LEN(a.MC)/5.0)>=b.number and b.number<>0
)
select SUBSTRING(b.MC,1,number*5) as MC from maco a left join @tb b on a.id=b.id
/*
MC
--------------------------------------------------
00001
0000100006
000010000600002
00001
0000100006
000010000600007
00001
0000100007
000010000700005
00001
0000100007
000010000700006
00002
0000200005
000020000500007
00001
0000100002
000010000200003
00001000020000300004
0000100002000030000400005
000010000200003000040000500006
00001000020000300004000050000600007
0000100002000030000400005000060000700008
000010000200003000040000500006000070000800009
00001000020000300004000050000600007000080000900010
(25 行受影响)
*/
