关闭

用语句拆分字符串(固定位数)

187人阅读 评论(0) 收藏 举报
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


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:195次
    • 积分:11
    • 等级:
    • 排名:千里之外
    • 原创:1篇
    • 转载:0篇
    • 译文:0篇
    • 评论:0条
    文章存档