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 行受影响)
*/

------解决方案--------------------
探讨

引用:

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'……