因为网络(电信)公司有用户上网的IP地址与IP地址映射表,需要将IP地址转换为特定的形式(去除“.”而在每个“.”中间部分如不满3位则在其前添加0。
有表iptrans如下:
字段与数据:
IP
===============
202.198.1.1
198.17.45.5
42.198.446.34
将其转化为字符串:
202198001001
198017045005
042198446034
SQL语句如下:
select ip
,case len(ltrim(rtrim(substring(ip,0,charindex('.',ip)))))
when 1 then '0'+'0'+ ltrim(rtrim(substring(ip,0,charindex('.',ip))))
when 2 then '0'+ ltrim(rtrim(substring(ip,0,charindex('.',ip))))
else ltrim(rtrim(substring(ip,0,charindex('.',ip))))
end
+case len(ltrim(rtrim(substring((substring(ip,charindex('.',ip)+1,15)),0,charindex('.',(substring(ip,charindex('.',ip)+1,15)))))))
when 1 then '0'+'0'+ ltrim(rtrim(substring((substring(ip,charindex('.',ip)+1,15)),0,charindex('.',(substring(ip,charindex('.',ip)+1,15))))))
when 2 then '0'+ ltrim(rtrim(substring((substring(ip,charindex('.',ip)+1,15)),0,charindex('.',(substring(ip,charindex('.',ip)+1,15))))))
else ltrim(rtrim(substring((substring(ip,charindex('.',ip)+1,15)),0,charindex('.',(substring(ip,charindex('.',ip)+1,15))))))
end
+case len(ltrim(rtrim(substring((substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15)),0,charindex('.',(substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15)))))))
when 1 then '0'+'0'+ ltrim(rtrim(substring((substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15)),0,charindex('.',(substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15))))))
when 2 then '0'+ ltrim(rtrim(substring((substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15)),0,charindex('.',(substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15))))))
else ltrim(rtrim(substring((substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15)),0,charindex('.',(substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15))))))
end
+case len(ltrim(rtrim(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)))),15)),2,5))))
when 1 then '0'+'0'+ ltrim(rtrim(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)))),15)),2,5)))
when 2 then '0'+ ltrim(rtrim(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)))),15)),2,5)))
else ltrim(rtrim(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)))),15)),2,5)))
end a
from iptrans
有表iptrans如下:
字段与数据:
IP
===============
202.198.1.1
198.17.45.5
42.198.446.34
将其转化为字符串:
202198001001
198017045005
042198446034
SQL语句如下:
select ip
,case len(ltrim(rtrim(substring(ip,0,charindex('.',ip)))))
when 1 then '0'+'0'+ ltrim(rtrim(substring(ip,0,charindex('.',ip))))
when 2 then '0'+ ltrim(rtrim(substring(ip,0,charindex('.',ip))))
else ltrim(rtrim(substring(ip,0,charindex('.',ip))))
end
+case len(ltrim(rtrim(substring((substring(ip,charindex('.',ip)+1,15)),0,charindex('.',(substring(ip,charindex('.',ip)+1,15)))))))
when 1 then '0'+'0'+ ltrim(rtrim(substring((substring(ip,charindex('.',ip)+1,15)),0,charindex('.',(substring(ip,charindex('.',ip)+1,15))))))
when 2 then '0'+ ltrim(rtrim(substring((substring(ip,charindex('.',ip)+1,15)),0,charindex('.',(substring(ip,charindex('.',ip)+1,15))))))
else ltrim(rtrim(substring((substring(ip,charindex('.',ip)+1,15)),0,charindex('.',(substring(ip,charindex('.',ip)+1,15))))))
end
+case len(ltrim(rtrim(substring((substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15)),0,charindex('.',(substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15)))))))
when 1 then '0'+'0'+ ltrim(rtrim(substring((substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15)),0,charindex('.',(substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15))))))
when 2 then '0'+ ltrim(rtrim(substring((substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15)),0,charindex('.',(substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15))))))
else ltrim(rtrim(substring((substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15)),0,charindex('.',(substring((substring(ip,charindex('.',ip)+1,15)),(charindex('.',(substring(ip,charindex('.',ip)+1,15)))+1),15))))))
end
+case len(ltrim(rtrim(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)))),15)),2,5))))
when 1 then '0'+'0'+ ltrim(rtrim(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)))),15)),2,5)))
when 2 then '0'+ ltrim(rtrim(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)))),15)),2,5)))
else ltrim(rtrim(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)))),15)),2,5)))
end a
from iptrans