当前页面位置: » 丰搜网 » 文档中心 » 详细内容
如何将作为字符串保存的ip地址变为二进制数值？
对于sql server 2000，你可以使用下列函数:
create function dbo.ipaddrstr2bin(@strip varchar(15), @validate bit = 1)
returns binary(4)
as
begin
if @validate = 1
begin
-- only digits and dots
if @strip like '%[^.0-9]%' return (null)
-- number of dots must be 3
if len(@strip) - len(replace(@strip, '.', '')) != 3 return (null)
-- all octets must be specified
if @strip not like '%_%.%_%.%_%.%_%' return (null)
end
declare @oct1 binary(1),
@oct2 binary(1),
@oct3 binary(1),
@oct4 binary(1)
set @oct1 = cast(cast(left(@strip, charindex('.', @strip) - 1) as int) as
binary(1))
set @oct2 = cast(cast(substring(@strip, charindex('.', @strip) + 1,
charindex('.', @strip, charindex('.', @strip) + 1) - charindex('.', @strip)
- 1) as int) as binary(1))
set @oct3 = cast(cast(substring(@strip, charindex('.', @strip,
charindex('.', @strip) + 1) + 1, (len(@strip) - charindex('.',
reverse(@strip)) + 1) - (charindex('.', @strip, charindex('.', @strip) + 1))
- 1) as int) as binary(1))
set @oct4 = cast(cast(right(@strip, len(@strip) - (len(@strip) -
charindex('.', reverse(@strip)) + 1)) as int) as binary(1))
if @validate = 1
begin
if not(
(@oct1 between 0x01 and 0xff) and
(@oct2 between 0x00 and 0xff) and
(@oct3 between 0x00 and 0xff) and
(@oct4 between 0x00 and 0xff)
) return(null)
end
return (@oct1 + @oct2 + @oct3 + @oct4)
end
go
使用例子:
select dbo.ipaddrstr2bin('172.29.23.2', 0)
对于 sql server较早的版本，你可以抽出代码，然后直接使用，或者创建如下所示的存储过程：
create procedure dbo.spipaddrstr2bin
@strip varchar(15),
@binip binary(4) output,
@validate bit = 1
as
if @validate = 1
begin
-- only digits and dots
if @strip like '%[^.0-9]%' return (null)
-- number of dots must be 3
if len(@strip) - len(replace(@strip, '.', '')) != 3 return (null)
-- all octets must be specified
if @strip not like '%_%.%_%.%_%.%_%' return (null)
end
declare @oct1 binary(1),
@oct2 binary(1),
@oct3 binary(1),
@oct4 binary(1)
set @oct1 = cast(cast(left(@strip, charindex('.', @strip) - 1) as int) as
binary(1))
set @oct2 = cast(cast(substring(@strip, charindex('.', @strip) + 1,
charindex('.', @strip, charindex('.', @strip) + 1) - charindex('.',
@strip) - 1) as int) as binary(1))
set @oct3 = cast(cast(substring(@strip, charindex('.', @strip,
charindex('.', @strip) + 1) + 1, (len(@strip) - charindex('.',
reverse(@strip)) + 1) - (charindex('.', @strip, charindex('.', @strip) +
1)) - 1) as int) as binary(1))
set @oct4 = cast(cast(right(@strip, len(@strip) - (len(@strip) -
charindex('.', reverse(@strip)) + 1)) as int) as binary(1))
if @validate = 1
begin
if not(
(@oct1 between 0x01 and 0xff) and
(@oct2 between 0x00 and 0xff) and
(@oct3 between 0x00 and 0xff) and
(@oct4 between 0x00 and 0xff)
) return(null)
end
set @binip = @oct1 + @oct2 + @oct3 + @oct4
go
使用例子:
declare @binip binary(4)
exec dbo.spipaddrstr2bin '172.29.23.2', @binip output, 0
print @binip
注意: 如果你不需要或不想验证自己的字符串ip地址，可以完全删掉对它们进行处理的代码。
