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

with t1 as(select '1' col1 union allselect '1.22' col1 union allselect '1.22.333' col1 union allselect '1.22.333.4444' col1 union allselect '1.22.333.4444.55555' col1 union allselect '1.22.333.4444.55555.666666' col1 union allselect '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 n7from(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 col6from(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 col5from(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 col4from(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 col3from(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 col2from t1) t2) t3) t4) t5) t6