把如192.168.0.1这样的地址格式化为如192168000001

本文链接：https://blog.csdn.net/lhxiong/article/details/3936924

在网上搜索了很久.怎么样才能把如192.168.0.1这样的地址格式化为如192168000001这样的字符串,总是不合心意.于是花了点时间写了这么一段代码.

select
case charindex('.',ip)
     when 1 then 'IP1有误'
     when 2 then '00'+substring(ip,1,1)
     when 3 then '0'+substring(ip,1,2)
     when 4 then substring(ip,1,3)
     else 'IP1出错'
end +   --as ip1,
case charindex('.',substring(ip,charindex('.',ip)+1,15))
     when 1 then 'IP2有误'
     when 2 then '00'+substring(substring(ip,charindex('.',ip)+1,15),1,1)
     when 3 then '0'+substring(substring(ip,charindex('.',ip)+1,15),1,2)
     when 4 then substring(substring(ip,charindex('.',ip)+1,15),1,3)
     else 'IP2出错'
end +   -- as ip2,
case charindex('.',substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15))
     when 1 then 'IP3有误'
     when 2 then '00'+substring(substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15),1,1)
     when 3 then '0'+substring(substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15),1,2)
     when 4 then substring(substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15),1,3)
     else 'IP3出错'
end +   ---as ip3,
case len(substring(substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15),charindex('.',substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15))+1,15))+1
     when 1 then 'IP4有误'
     when 2 then '00'+substring(substring(substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15),charindex('.',substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15))+1,15),1,1)
     when 3 then '0'+substring(substring(substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15),charindex('.',substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15))+1,15),1,2)
     when 4 then substring(substring(substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15),charindex('.',substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15))+1,15),1,3)
     else 'IP4出错'
end as IPnum, --ip4,
substring(ip,charindex('.',ip)+1,15) as k1,
substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15) as k2,
substring(substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15),charindex('.',substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15))+1,15) as k3,
len(substring(substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15),charindex('.',substring(substring(ip,charindex('.',ip)+1,15),charindex('.',substring(ip,charindex('.',ip)+1,15))+1,15))+1,15)) as len_k3

from IPmac
go