我在 IP 表中记录了大量的IP地址 类型是 varchar
如: 172.168.215.1
172.168.215.9
172.168.215.10
172.168.215.12
等等
我打算新加一个IP地址,它是原有表中的显示最大的一个IP值+1
如上面 最大的为 172.168.215.12 ,加入的就应该是 172.168.215.13我试过用max()函数,由于是字符类型判断不准,谁能写一段存储过程来实现?谢谢
如: 172.168.215.1
172.168.215.9
172.168.215.10
172.168.215.12
等等
我打算新加一个IP地址,它是原有表中的显示最大的一个IP值+1
如上面 最大的为 172.168.215.12 ,加入的就应该是 172.168.215.13我试过用max()函数,由于是字符类型判断不准,谁能写一段存储过程来实现?谢谢
insert into @t select rtrim('172.168.215.9 ')
insert into @t select rtrim('172.168.215.10')
insert into @t select rtrim('172.168.215.12')select
rtrim(cast(substring(IP, 1,3) as int))+'.'+
rtrim(cast(substring(IP, 4,3) as int))+'.'+
rtrim(cast(substring(IP, 7,3) as int))+'.'+
rtrim(substring(IP,10,3)+1) as NewIP
from
(select
max(right('00'+PARSENAME(IP,4),3)+
right('00'+PARSENAME(IP,3),3)+
right('00'+PARSENAME(IP,2),3)+
right('00'+PARSENAME(IP,1),3)) as IP
from
@t) a/*
NewIP
---------------------------------------------------
172.168.215.13
*/
FROM sysobjects
WHERE name = N'IP'
AND type = 'U')
DROP TABLE IP
GO
Create table IP(
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[IP] varchar(15)
)INSERT INTO IP
SELECT '172.168.215.10' UNION ALL
SELECT '172.168.215.1'UNION ALL
SELECT '172.168.215.12'UNION ALL
SELECT '172.168.215.9' --------不用存储过程可以求出来的示例--------SELECT SubOne,subTwo,SUBSTRING(subIP,0,CHARINDEX('.',subIP)) AS SubThree,
CAST(SUBSTRING(subIP,CHARINDEX('.',subIP)+1,len(subIP)) AS int) AS SubFour,IP
FROM (
SELECT SubOne,SUBSTRING(subIP,0,CHARINDEX('.',subIP)) AS subTwo,
SUBSTRING(subIP,CHARINDEX('.',subIP)+1,len(subIP)) AS subIP,IP
FROM (
SELECT SUBSTRING(IP,0,CHARINDEX('.',IP)) AS subOne,
SUBSTRING(IP,CHARINDEX('.',IP)+1,len(IP)) AS subIP,IP FROM IP
) A
) B ORDER BY SubOne,SubTwo,SubThree,SubFour
--------不用存储过程可以插最大IP如下--------
INSERT INTO IP
SELECT SubOne+'.'+ subTwo+'.'+SubThree+'.'+ CAST(SubFour+1 AS varchar(4)) FROM (
SELECT top 1 SubOne,subTwo,SUBSTRING(subIP,0,CHARINDEX('.',subIP)) AS SubThree,
CAST(SUBSTRING(subIP,CHARINDEX('.',subIP)+1,len(subIP)) AS int) AS SubFour,IP
FROM(
SELECT SubOne,SUBSTRING(subIP,0,CHARINDEX('.',subIP)) AS subTwo,
SUBSTRING(subIP,CHARINDEX('.',subIP)+1,len(subIP)) AS subIP,IP
FROM (
SELECT SUBSTRING(IP,0,CHARINDEX('.',IP)) AS subOne,
SUBSTRING(IP,CHARINDEX('.',IP)+1,len(IP)) AS subIP,IP FROM IP
) A
) B ORDER BY SubOne DESC,SubTwo DESC,SubThree DESC,SubFour DESC
) A
GO