sql中 ip地址转化成数值的问题，希望大家帮忙看一下！

Question

补充一下  ：
从网上找到这样一个函数  但是不知道怎么才能用上  
create function ip2number (@ip varchar(16))returns bigint
as
begin
set @ip=@ip+'.'
declare @pos tinyint
declare @num bigint
declare @bin int,@off int
set @bin=16777216 
set @off=0
set @num=0
while @bin>=1
 begin
  set @pos=@off+1
  set @off=charindex('.',@ip,@pos)
  set @num=@num+convert(int,substring(@ip,@pos,@off-@pos))*@bin
  set @bin=@bin/256
 end
return @num
end
go 

Answer 1

用分割字符串的方法，吧 IP 分割成四个数字，然后用公司：A*256*256*256+B*256*256+c*256+D 计算出来就可以了

Answer 2

ip1   ip2  是 “12.154.161.0”  这样的    --------------------------------
“用分割字符串的方法，吧 IP 分割成四个数字，然后用公司：A*256*256*256+B*256*256+c*256+D 计算出来就可以了”怎么分割字符串？

Answer 3

--参考这个:if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_IP2Int]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_IP2Int]
GO--1. 字符串IP地址转换成IP数值函数。
CREATE FUNCTION dbo.f_IP2Int(
@ip char(15)
)RETURNS bigint
AS
BEGIN
DECLARE @re bigint
SET @re=0
SELECT @re=@re+LEFT(@ip,CHARINDEX('.',@ip+'.')-1)*ID
,@ip=STUFF(@ip,1,CHARINDEX('.',@ip+'.'),'')
FROM(
SELECT ID=CAST(16777216 as bigint)
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1)a
RETURN(@re)
END
GO
/*===========================================================*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_Int2IP]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_Int2IP]
GO--1. 字符串IP地址转换成IP数值函数。
CREATE FUNCTION dbo.f_Int2IP(
@IP bigint
)RETURNS varchar(15)
AS
BEGIN
DECLARE @re varchar(15)
SET @re=''
SELECT @re=@re+'.'+CAST(@IP/ID as varchar)
,@IP=@IP%ID
from(
SELECT ID=CAST(16777216 as bigint)
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1)a
RETURN(STUFF(@re,1,1,''))
END
GO

Answer 4

表结构  
我也知道是用 A*256*256*256+B*256*256+c*256+D来计算 
但是在sql里不知道怎么用语句来写

Answer 5

declare @ip varchar(200)
set @ip = '12.154.161.0'select parsename(@ip,4)*256*256*256+parsename(@ip,3)*256*256+parsename(@ip,2)*256+parsename(@ip,1) as IPIP            
----------- 
211460352（所影响的行数为 1 行）
这样?

Answer 6

IPs1   ips2   都空着呢   不知道用什么语句补上数据

Answer 7

if object_id('[IPall]') is not null drop table [IPall]
go
create table [IPall]([ip1] varchar(16),[ip2] varchar(16),[ips1] bigint,[ips2] bigint)
insert [IPall]
select '192.168.1.18','202.55.6.38',null,nullselect * from [IPall]
go
--1.使用函数
create function ip2number (@ip varchar(16))returns bigint 
as 
begin 
set @ip=@ip+'.' 
declare @pos tinyint 
declare @num bigint 
declare @bin bigint,@off int 
set @bin=16777216 
set @off=0 
set @num=0 
while @bin>=1 
begin 
  set @pos=@off+1 
  set @off=charindex('.',@ip,@pos) 
  set @num=@num+convert(int,substring(@ip,@pos,@off-@pos))*@bin 
  set @bin=@bin/256 
end 
return @num 
end 
go select ip1,ip2,ips1=dbo.ip2number(ip1),ips2=dbo.ip2number(ip2)
from IPall
/*
ip1              ip2              ips1                 ips2
---------------- ---------------- -------------------- --------------------
192.168.1.18     202.55.6.38      3232235794           3392603686
*/
drop function ip2number--2.使用parsename
select  ip1,ip2
,ips1=cast(parsename(ip1,4) as bigint)*256*256*256+parsename(ip1,3)*256*256+parsename(ip1,2)*256+parsename(ip1,1)
,ips2=cast(parsename(ip2,4) as bigint)*256*256*256+parsename(ip2,3)*256*256+parsename(ip2,2)*256+parsename(ip2,1)
from IPall
/*
ip1              ip2              ips1                 ips2
---------------- ---------------- -------------------- --------------------
192.168.1.18     202.55.6.38      3232235794           3392603686
*/

Answer 8

if object_id('[IPall]') is not null drop table [IPall]
go
create table [IPall]([ip1] varchar(16),[ip2] varchar(16),[ips1] bigint,[ips2] bigint)
insert [IPall]
select '192.168.1.18','202.55.6.38',null,nullselect * from [IPall]
go
--1.使用函数
create function ip2number (@ip varchar(16))returns bigint 
as 
begin 
set @ip=@ip+'.' 
declare @pos tinyint 
declare @num bigint 
declare @bin bigint,@off int  --注意，要定义为bigint
set @bin=16777216 
set @off=0 
set @num=0 
while @bin>=1 
begin 
  set @pos=@off+1 
  set @off=charindex('.',@ip,@pos) 
  set @num=@num+convert(int,substring(@ip,@pos,@off-@pos))*@bin 
  set @bin=@bin/256 
end 
return @num 
end 
go select ip1,ip2,ips1=dbo.ip2number(ip1),ips2=dbo.ip2number(ip2)
from IPall
/*
ip1              ip2              ips1                 ips2
---------------- ---------------- -------------------- --------------------
192.168.1.18     202.55.6.38      3232235794           3392603686
*/
drop function ip2number--2.使用parsename
select  ip1,ip2
,ips1=cast(parsename(ip1,4) as bigint)*256*256*256+parsename(ip1,3)*256*256+parsename(ip1,2)*256+parsename(ip1,1)
,ips2=cast(parsename(ip2,4) as bigint)*256*256*256+parsename(ip2,3)*256*256+parsename(ip2,2)*256+parsename(ip2,1)
from IPall
/*
ip1              ip2              ips1                 ips2
---------------- ---------------- -------------------- --------------------
192.168.1.18     202.55.6.38      3232235794           3392603686
*/

Answer 9

在查询分析器里执行:update IPall 
set ips1=cast(parsename(ip1,4) as bigint)*256*256*256+parsename(ip1,3)*256*256+parsename(ip1,2)*256+parsename(ip1,1),
ips2=cast(parsename(ip2,4) as bigint)*256*256*256+parsename(ip2,3)*256*256+parsename(ip2,2)*256+parsename(ip2,1)这样你要的结果都出来了.

Answer 10

declare @test_ip varchar(100),@mid_ip bigint,@ip2int bigint
set @test_ip='192.168.2.100'
set @mid_ip=cast(left(@test_ip,CHARINDEX('.',@test_ip)-1) as bigint)--A类地址
set @ip2int=@mid_ip*256*256*256 
set @test_ip=right(@test_ip,len(@test_ip)-CHARINDEX('.',@test_ip))
set @mid_ip=cast(left(@test_ip,CHARINDEX('.',@test_ip)-1) as bigint)--B类地址
set @ip2int=@ip2int+@mid_ip*256*256
set @test_ip=right(@test_ip,len(@test_ip)-CHARINDEX('.',@test_ip))
set @mid_ip=cast(left(@test_ip,CHARINDEX('.',@test_ip)-1) as bigint)--B类地址
set @ip2int=@ip2int+@mid_ip*256
set @test_ip=right(@test_ip,len(@test_ip)-CHARINDEX('.',@test_ip))
set @mid_ip=cast(@test_ip as bigint)--d类地址
set @ip2int=@ip2int+@mid_ip看上面的例子就很清楚了

Answer 11

create table ippAll(
ip nvarchar(256),
ip2 nvarchar(256),
ips1 as rtrim(parsename(ip,1)+parsename(ip,2)*255+parsename(ip,3)*255*255+parsename(ip,4)*255*255*255),
ips2 as rtrim(parsename(ip2,1)+parsename(ip2,2)*255+parsename(ip2,3)*255*255+parsename(ip2,4)*255*255*255),
name nvarchar(256)
)insert ippAll values('11.34.56.56','23.3.5.66','北京')
insert ippAll values('13.44.56.156','24.53.52.166','北京')select * from ippAll
/*
ip                   ip2                  ips1         ips2         name
-------------------- -------------------- ------------ ------------ ----------------
11.34.56.56          23.3.5.66            184620311    381568041    北京
13.44.56.156         24.53.52.166         218433411    401412751    北京(2 row(s) affected)
*/

Answer 12

如果是对表中的数据进行更新
update IPall 
set ips1=cast(parsename(ip1,4) as bigint)*256*256*256+parsename(ip1,3)*256*256+parsename(ip1,2)*256+parsename(ip1,1),
ips2=cast(parsename(ip2,4) as bigint)*256*256*256+parsename(ip2,3)*256*256+parsename(ip2,2)*256+parsename(ip2,1)如果是在建表时让系统对IPs1,IPs2自动填充的话参考19楼推荐让系统自动进行计算

Answer 13

更正一下create table ippAll(
ip nvarchar(256),
ip2 nvarchar(256),
ips1 as rtrim( parsename(ip,1 ) +parsename(ip,2 )*255+parsename(ip,3 )*255*255 + cast(parsename(ip,4) as bigint)*255*255*255 ),
ips2 as rtrim( parsename(ip2,1) +parsename(ip2,2 )*255+parsename(ip2,3 )*255*255 + cast(parsename(ip2,4) as bigint)*255*255*255 ),
name nvarchar(256)
)

Answer 14

Create FUNCTION ip2bigint (@test_ip varchar(20))
RETURNS bigint
AS
BEGIN
declare @mid_ip bigint
declare @ip2int bigint
set @mid_ip=cast(left(@test_ip,CHARINDEX('.',@test_ip)-1) as bigint)--A类地址
set @ip2int=@mid_ip*256*256*256 
set @test_ip=right(@test_ip,len(@test_ip)-CHARINDEX('.',@test_ip))--B类地址
set @mid_ip=cast(left(@test_ip,CHARINDEX('.',@test_ip)-1) as bigint)
set @ip2int=@ip2int+@mid_ip*256*256
set @test_ip=right(@test_ip,len(@test_ip)-CHARINDEX('.',@test_ip))--C类地址
set @mid_ip=cast(left(@test_ip,CHARINDEX('.',@test_ip)-1) as bigint)
set @ip2int=@ip2int+@mid_ip*256
set @test_ip=right(@test_ip,len(@test_ip)-CHARINDEX('.',@test_ip)) --D类地址
set @mid_ip=cast(@test_ip as bigint)
set @ip2int=@ip2int+@mid_ip
RETURN @ip2intEND
GO给你一个函数，你直接使用就是了update tb set ips1=dbo.ip2bigint(ip1),ips2=dbo.ip2bigint(ip2)

Answer 15

批量转换declare @ip varchar(100)
declare @ip1 bigint
declare @sql varchar(100)
declare @a1 bigint
declare @a2 bigint
declare @a3 bigint
declare @a4 bigint
declare @com varchar(100)
declare @str varchar(8000),@start int,@end int
select ip1 into #t from ip_table
while (select COUNT(1) from #t) >0
begin 
select top 1 @ip=ip1 from #t
set @str =@ip+'.'
set @start = 1
set @end = charindex('.',@str,@start)  --第一次出现分割字符的下标位置


select @a1=substring(@str,@start,@end-@start)  --输出成员;(@end-@start)的值等于成员的长度,不包含分割字符。

set @start = @end+1 set @end = charindex('.',@str,@start)  --输出最后一个成员后，@end =0，则退出循环。
--
select @a2=substring(@str,@start,@end-@start)  --输出成员;(@end-@start)的值等于成员的长度,不包含分割字符。

set @start = @end+1 set @end = charindex('.',@str,@start)  --输出最后一个成员后，@end =0，则退出循环。
--
select @a3=substring(@str,@start,@end-@start)  --输出成员;(@end-@start)的值等于成员的长度,不包含分割字符。

set @start = @end+1 set @end = charindex('.',@str,@start)  --输出最后一个成员后，@end =0，则退出循环。

--
select @a4=substring(@str,@start,@end-@start)  --输出成员;(@end-@start)的值等于成员的长度,不包含分割字符。



print @a1
print @a2
print @a3
print @a4
-- A*256*256*256 + B*256*256 + C*256 + D
select @ip1 =@a1*256*256*256 + @a2*256*256 + @a3*256 + @a4
print @ip1
exec (@sql)
delete from #t where end_ip1=@ip
endtruncate table #t
drop table #t

Answer 16

"update IPall 
set ips1=cast(parsename(ip1,4) as bigint)*256*256*256+parsename(ip1,3)*256*256+parsename(ip1,2)*256+parsename(ip1,1),
ips2=cast(parsename(ip2,4) as bigint)*256*256*256+parsename(ip2,3)*256*256+parsename(ip2,2)*256+parsename(ip2,1)"
这样应该能解决我的问题  
但是我在运行的时候提示 ：
nvarcher转换为bigint时出错
不知道时哪里造成的 
表结构：ip1  varcher（50）
ip2  varcher（50）
ips1 bigint  8
ips2 bigint  8

Answer 17

cast(parsename(ip2,4) as bigint)
这里出错了

Answer 18

不行啊  不是“cast(parsename(ip2,4) as bigint) ”的问题 
去了case（）后   提示int类型溢出 
我把parsename(ip1,1)去掉   可以正常运行和计算   
是不是超出bigint的范围了？ 

Answer 19

update IPall 
set ips1=cast(parsename(ip1,4) as bigint)*256*256*256+parsename(ip1,3)*256*256+parsename(ip1,2)*256+parsename(ip1,1),
ips2=cast(parsename(ip2,4) as bigint)*256*256*256+parsename(ip2,3)*256*256+parsename(ip2,2)*256+parsename(ip2,1)这样会有问题吗？

Answer 20

你原来的字段是否够长还是个问题。
先运行一下下面语句再运行上面的：
alter table IPall alter column ips1 bigint
alter table IPall alter column ips2 bigint

Answer 21

实在无语，也很失望，函数和表都没搞懂，给了答案都不会用的家伙，还怎么能做数据库呢，现在DBA就这么泛滥成灾了啊