拆字符串
MC
000010000600002
000010000600007
000010000700005
000010000700006
000020000500007

想要拆成下面的结果：
MC
00001
0000100006
000010000600002
000010000600007
0000100007
000010000700005
000010000700006
00002
0000200005
000020000500007

求简单的实现方式，最好是一条SQL语句！！
在此感谢各位！！

[解决办法]

SQL code

declare @tb table(id int identity(1,1),MC varchar(50))insert into @tbselect '000010000600002' union allselect '000010000600007' union allselect '000010000700005' union allselect '000010000700006' union allselect '000020000500007' union allselect '00001000020000300004000050000600007000080000900010';with maco as(select distinct id,b.number from @tb aleft join master..spt_values b on CEILING(LEN(a.MC)/5.0)>=b.numberwhere 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--------------------------------------------------00001000010000600001000060000200001000010000600001000060000700001000010000700001000070000500001000010000700001000070000600002000020000500002000050000700001000010000200001000020000300001000020000300004000010000200003000040000500001000020000300004000050000600001000020000300004000050000600007000010000200003000040000500006000070000800001000020000300004000050000600007000080000900001000020000300004000050000600007000080000900010(25 行受影响)*/
[解决办法]
探讨

引用:

MC是5位一级，最大长度为50



SQL code

--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO 
CREATE TABLE #tb([MC] VARCHAR(15))
INSERT #tb
SELECT '000010000600002'……