用语句拆分字符串（固定位数）

with t1 as
(
select '1' col1 union all
select '1.22' col1 union all
select '1.22.333' col1 union all
select '1.22.333.4444' col1 union all
select '1.22.333.4444.55555' col1 union all
select '1.22.333.4444.55555.666666' col1 union all
select '1.22.333.4444.55555.666666.7777777' col1
)

select col1, n1, n2, n3, n4, n5
, case when col6 like '%.%' then SUBSTRING(col6, 1, patindex('%.%', col6)-1) else col6 end as n6
, case when col6 like '%.%' then SUBSTRING(col6, patindex('%.%', col6)+1, 100) end as n7
from(
select col1, n1, n2, n3, n4
, case when col5 like '%.%' then SUBSTRING(col5, 1, patindex('%.%', col5)-1) else col5 end as n5
, case when col5 like '%.%' then SUBSTRING(col5, patindex('%.%', col5)+1, 100) end as col6
from(
select col1, n1, n2, n3
, case when col4 like '%.%' then SUBSTRING(col4, 1, patindex('%.%', col4)-1) else col4 end as n4
, case when col4 like '%.%' then SUBSTRING(col4, patindex('%.%', col4)+1, 100) end as col5
from(
select col1, n1, n2
, case when col3 like '%.%' then SUBSTRING(col3, 1, patindex('%.%', col3)-1) else col3 end as n3
, case when col3 like '%.%' then SUBSTRING(col3, patindex('%.%', col3)+1, 100) end as col4
from(
select col1, n1
, case when col2 like '%.%' then SUBSTRING(col2, 1, patindex('%.%', col2)-1) else col2 end as n2
, case when col2 like '%.%' then SUBSTRING(col2, patindex('%.%', col2)+1, 100) end as col3
from(
select col1
, case when col1 like '%.%' then SUBSTRING(col1, 1, patindex('%.%', col1)-1) else col1 end as n1
, case when col1 like '%.%' then SUBSTRING(col1, patindex('%.%', col1)+1, 100) end as col2
from t1) t2) t3) t4) t5) t6