用于在 SQLServer 中存储 ip 地址的数据类型

在 SQLServer 中存储 IP 地址应该选择哪种数据类型？

通过选择正确的数据类型，按 IP 地址过滤是否足够简单？

117902 次浏览
小开

您可以使用 varchar。 IPv4的长度是静态的，但 IPv6的长度可能是高度可变的。

除非您有充分的理由将其存储为二进制文件，否则请坚持使用字符串(文本)类型。

小开

我通常使用一个普通的旧 VARCHAR 过滤 IPAddress 工作得很好。

如果你想过滤 IP 地址的范围，我会把它分成四个整数。

小开

sys.dm_exec_connections在 SQLServer2005SP1之后使用 varchar (48)。听起来不错，特别是如果你想用它来比较你的价值。

实际上，IPv6在一段时间内还不会成为主流，所以我更喜欢4 tinyint 路由。说到这里，我使用 varchar (48)是因为我必须使用 sys.dm_exec_connections..。

否则，Mark Redman 的回答提到了之前的一个 SO辩论问题。

小开
最佳答案

技术上正确的存储 IPv4的方法是二进制(4) ，因为它实际上就是二进制(不，甚至不是一个 INT32/INT (4) ，我们都知道并喜欢的数字文本形式(255.255.255.255)只是它的二进制内容的显示转换)。

如果你这样做，你会希望函数转换成文本显示格式:

下面是如何将文本显示形式转换为二进制:

CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
DECLARE @bin AS BINARY(4)


SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))


RETURN @bin
END
go

下面是如何将二进制文件转换回文本显示形式:

CREATE FUNCTION dbo.fnDisplayIPv4(@ip AS BINARY(4)) RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @str AS VARCHAR(15)


SELECT @str = CAST( CAST( SUBSTRING( @ip, 1, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
+ CAST( CAST( SUBSTRING( @ip, 2, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
+ CAST( CAST( SUBSTRING( @ip, 3, 1) AS INTEGER) AS VARCHAR(3) ) + '.'
+ CAST( CAST( SUBSTRING( @ip, 4, 1) AS INTEGER) AS VARCHAR(3) );


RETURN @str
END;
go

下面是如何使用它们的演示:

SELECT dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go


SELECT dbo.fnDisplayIPv4( 0xC04144C9 )
-- should return '192.65.68.201'
go

Finally, when doing lookups and compares, always use the binary form if you want to be able to leverage your indexes.

更新:

I wanted to add that one way to address the inherent performance problems of scalar UDFs in SQL Server, but still retain the code-reuse of a function is to use an iTVF (inline table-valued function) instead. Here's how the first function above (string to binary) can be re-written as an iTVF:

CREATE FUNCTION dbo.itvfBinaryIPv4(@ip AS VARCHAR(15)) RETURNS TABLE
AS RETURN (
SELECT CAST(
CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
+  CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
+  CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
+  CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))
AS BINARY(4)) As bin
)
go

下面是这个例子:

SELECT bin FROM dbo.fnBinaryIPv4('192.65.68.201')
--should return 0xC04144C9
go

下面介绍如何在 INSERT 中使用它

INSERT INTo myIpTable
SELECT {other_column_values,...},
(SELECT bin FROM dbo.itvfBinaryIPv4('192.65.68.201'))
小开

谢谢，巴里。我正在建立一个 IP 块分配系统只能以二进制的形式存储。

我将 IP 块的 CIDR 表示(ex: 192.168.1.0/24)存储在一个 varchar 字段中，并使用2个计算字段来保存块的起始和结束的二进制形式。从那里，我可以运行快速查询，看看给定的块是否已经分配，或者是否可以自由分配。

我修改了你的函数来计算结束 IP 地址，如下所示:

CREATE FUNCTION dbo.fnDisplayIPv4End(@block AS VARCHAR(18)) RETURNS BINARY(4)
AS
BEGIN
DECLARE @bin AS BINARY(4)
DECLARE @ip AS VARCHAR(15)
DECLARE @size AS INT


SELECT @ip = Left(@block, Len(@block)-3)
SELECT @size = Right(@block, 2)


SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
+ CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))


SELECT @bin = CAST(@bin + POWER(2, 32-@size) AS BINARY(4))
RETURN @bin
END;
go
小开

下面是一些代码，它们可以以 varchar 格式将 IPV4或 IPv6转换为二进制(16)并返回。这是我能想到的最小的形状。它应该能够很好地建立索引，并提供一种相对简单的方法来对子网进行过滤。需要 SQLServer2005或更高版本。不知道是不是完全防弹。希望这个能帮上忙。

-- SELECT dbo.fn_ConvertIpAddressToBinary('2002:1ff:6c2::1ff:6c2')
-- SELECT dbo.fn_ConvertIpAddressToBinary('10.4.46.2')
-- SELECT dbo.fn_ConvertIpAddressToBinary('bogus')


ALTER FUNCTION dbo.fn_ConvertIpAddressToBinary
(
@ipAddress VARCHAR(39)
)
RETURNS BINARY(16) AS
BEGIN
DECLARE
@bytes BINARY(16), @vbytes VARBINARY(16), @vbzone VARBINARY(2)
, @colIndex TINYINT, @prevColIndex TINYINT, @parts TINYINT, @limit TINYINT
, @delim CHAR(1), @token VARCHAR(4), @zone VARCHAR(4)


SELECT
@delim = '.'
, @prevColIndex = 0
, @limit = 4
, @vbytes = 0x
, @parts = 0
, @colIndex = CHARINDEX(@delim, @ipAddress)


IF @colIndex = 0
BEGIN
SELECT
@delim = ':'
, @limit = 8
, @colIndex = CHARINDEX(@delim, @ipAddress)
WHILE @colIndex > 0
SELECT
@parts = @parts + 1
, @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1)
SET @colIndex = CHARINDEX(@delim, @ipAddress)


IF @colIndex = 0
RETURN NULL
END


SET @ipAddress = @ipAddress + @delim


WHILE @colIndex > 0
BEGIN
SET @token = SUBSTRING(@ipAddress, @prevColIndex + 1, @Colindex - @prevColIndex - 1)


IF @delim = ':'
BEGIN
SET  @zone = RIGHT('0000' + @token, 4)


SELECT
@vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(2)')
, @vbytes = @vbytes + @vbzone


IF @token = ''
WHILE @parts + 1 < @limit
SELECT
@vbytes = @vbytes + @vbzone
, @parts = @parts + 1
END
ELSE
BEGIN
SET @zone = SUBSTRING('' + master.sys.fn_varbintohexstr(CAST(@token AS TINYINT)), 3, 2)


SELECT
@vbzone = CAST('' AS XML).value('xs:hexBinary(sql:variable("@zone"))', 'varbinary(1)')
, @vbytes = @vbytes + @vbzone
END


SELECT
@prevColIndex = @colIndex
, @colIndex = CHARINDEX(@delim, @ipAddress, @colIndex + 1)
END


SET @bytes =
CASE @delim
WHEN ':' THEN @vbytes
ELSE 0x000000000000000000000000 + @vbytes
END


RETURN @bytes


END

-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x200201FF06C200000000000001FF06C2)
-- SELECT dbo.fn_ConvertBinaryToIpAddress(0x0000000000000000000000000A0118FF)


ALTER FUNCTION [dbo].[fn_ConvertBinaryToIpAddress]
(
@bytes BINARY(16)
)
RETURNS VARCHAR(39) AS
BEGIN
DECLARE
@part VARBINARY(2)
, @colIndex TINYINT
, @ipAddress VARCHAR(39)


SET @ipAddress = ''


IF SUBSTRING(@bytes, 1, 12) = 0x000000000000000000000000
BEGIN
SET @colIndex = 13
WHILE @colIndex <= 16
SELECT
@part = SUBSTRING(@bytes, @colIndex, 1)
, @ipAddress = @ipAddress
+ CAST(CAST(@part AS TINYINT) AS VARCHAR(3))
+ CASE @colIndex WHEN 16 THEN '' ELSE '.' END
, @colIndex = @colIndex + 1


IF @ipAddress = '0.0.0.1'
SET @ipAddress = '::1'
END
ELSE
BEGIN
SET @colIndex = 1
WHILE @colIndex <= 16
BEGIN
SET @part = SUBSTRING(@bytes, @colIndex, 2)
SELECT
@ipAddress = @ipAddress
+ CAST('' as xml).value('xs:hexBinary(sql:variable("@part") )', 'varchar(4)')
+ CASE @colIndex WHEN 15 THEN '' ELSE ':' END
, @colIndex = @colIndex + 2
END
END


RETURN @ipAddress


END
小开

给那些吸毒的人。NET 可以使用 IPAddress 类解析 IPv4/IPv6字符串并将其存储为 VARBINARY(16)。可以使用同一个类将 byte[]转换为字符串。如果要在 SQL 中转换 VARBINARY:

--SELECT
--  dbo.varbinaryToIpString(CAST(0x7F000001 AS VARBINARY(4))) IPv4,
--  dbo.varbinaryToIpString(CAST(0x20010DB885A3000000008A2E03707334 AS VARBINARY(16))) IPv6


--ALTER
CREATE
FUNCTION dbo.varbinaryToIpString
(
@varbinaryValue VARBINARY(16)
)
RETURNS VARCHAR(39)
AS
BEGIN
IF @varbinaryValue IS NULL
RETURN NULL
IF DATALENGTH(@varbinaryValue) = 4
BEGIN
RETURN
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 1, 1))) + '.' +
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 2, 1))) + '.' +
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 3, 1))) + '.' +
CONVERT(VARCHAR(3), CONVERT(INT, SUBSTRING(@varbinaryValue, 4, 1)))
END
IF DATALENGTH(@varbinaryValue) = 16
BEGIN
RETURN
sys.fn_varbintohexsubstring(0, @varbinaryValue,  1, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue,  3, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue,  5, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue,  7, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue,  9, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 11, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 13, 2) + ':' +
sys.fn_varbintohexsubstring(0, @varbinaryValue, 15, 2)
END


RETURN 'Invalid'
END
小开

因为我想同时处理 IPv4和 IPv6，所以我使用 VARBINARY(16)和以下 SQL CLR函数将 text IP 地址表示转换为字节和反向:

[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlBytes GetIPAddressBytesFromString (SqlString value)
{
IPAddress IP;


if (IPAddress.TryParse(value.Value, out IP))
{
return new SqlBytes(IP.GetAddressBytes());
}
else
{
return new SqlBytes();
}
}




[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlString GetIPAddressStringFromBytes(SqlBytes value)
{
string output;


if (value.IsNull)
{
output = "";
}
else
{
IPAddress IP = new IPAddress(value.Value);
output = IP.ToString();
}


return new SqlString(output);
}
小开

我喜欢沙岩的功能。但是我在 Dbo.fn _ ConvertIpAddressToBinary的代码中发现了一个错误。当您将@delim 连接到@ipAddress VARCHAR (39)时，它的传入参数太小。

SET @ipAddress = @ipAddress + @delim

你可以增加到40。或者更好的方法是使用一个更大的新变量并在内部使用它。这样你就不会失去最后一对大数。

SELECT dbo.fn_ConvertIpAddressToBinary('ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff')
小开

我使用 varchar(15)到目前为止一切都为我工作。插入，更新，选择。我刚刚开始一个应用程序，有 IP 地址，虽然我还没有做很多开发工作。

下面是 select 语句:

select * From dbo.Server
where  [IP] = ('132.46.151.181')
Go
小开

以下答案是基于 特纳特先生和 Jerry Birchler对这个问题的答案，但有以下改进:

  • 用 CONVERT()代替 二进制风格使用未记录的函数(sys.fn_varbintohexsubstring，fn_varbintohexstr)
  • 在 二进制风格中用 CONVERT()替换 XML“ hacks”(CAST('' as xml).value('xs:hexBinary()))
  • Fixed bug in Jerry Birchler's implementation of fn_ConvertIpAddressToBinary (as pointed out by C.Plock)
  • 添加少量语法糖

代码已经在 SQLServer2014和 SQL Server 2016中进行了测试(参见最后的测试用例)

网址

将4字节值转换为 IPV4，将16字节值转换为 IPV6字符串表示形式。注意，此函数不缩短地址。

ALTER FUNCTION dbo.IPAddressVarbinaryToString
(
@varbinaryValue VARBINARY( 16 )
)
RETURNS VARCHAR(39)
AS
BEGIN
IF @varbinaryValue IS NULL
RETURN NULL;
ELSE IF DATALENGTH( @varbinaryValue ) = 4
RETURN
CONVERT( VARCHAR(3), CONVERT(TINYINT, SUBSTRING( @varbinaryValue, 1, 1 ))) + '.' +
CONVERT( VARCHAR(3), CONVERT(TINYINT, SUBSTRING( @varbinaryValue, 2, 1 ))) + '.' +
CONVERT( VARCHAR(3), CONVERT(TINYINT, SUBSTRING( @varbinaryValue, 3, 1 ))) + '.' +
CONVERT( VARCHAR(3), CONVERT(TINYINT, SUBSTRING( @varbinaryValue, 4, 1 )));
ELSE IF DATALENGTH( @varbinaryValue ) = 16
RETURN
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue,  1, 2 ), 2 ) + ':' +
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue,  3, 2 ), 2 ) + ':' +
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue,  5, 2 ), 2 ) + ':' +
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue,  7, 2 ), 2 ) + ':' +
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue,  9, 2 ), 2 ) + ':' +
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue, 11, 2 ), 2 ) + ':' +
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue, 13, 2 ), 2 ) + ':' +
CONVERT( VARCHAR(4), SUBSTRING( @varbinaryValue, 15, 2 ), 2 );


RETURN 'Invalid';
END

测试案例:

SELECT dbo.IPAddressVarbinaryToString(0x00000000000000000000000000000000) -- 0000:0000:0000:0000:0000:0000:0000:0000 (no address shortening)
SELECT dbo.IPAddressVarbinaryToString(0x00010002000300400500060070000089) -- 0001:0002:0003:0040:0500:0600:7000:0089
SELECT dbo.IPAddressVarbinaryToString(0xC0A80148) -- 255.168.1.72
SELECT dbo.IPAddressVarbinaryToString(0x7F000001) -- 127.0.0.1 (no address shortening)
SELECT dbo.IPAddressVarbinaryToString(NULL) -- NULL

二进制地址

将 IPV4和 IPV6字符串表示分别转换为4字节和16字节的二进制值。注意，这个函数能够解析大多数(所有常用的)速记地址表示(例如127... 1和2001: db8: : 1319:370:7348)。 To force this function to always return 16 byte binary values uncomment leading 0s concatenation at the end of the function.

ALTER FUNCTION [dbo].[IPAddressStringToVarbinary]
(
@IPAddress VARCHAR( 39 )
)
RETURNS VARBINARY(16) AS
BEGIN


IF @ipAddress IS NULL
RETURN NULL;


DECLARE @bytes VARBINARY(16), @token VARCHAR(4),
@vbytes VARBINARY(16) = 0x, @vbzone VARBINARY(2),
@tIPAddress VARCHAR( 40 ),
@colIndex TINYINT,
@delim CHAR(1) = '.',
@prevColIndex TINYINT = 0,
@parts TINYINT = 0, @limit TINYINT = 4;


-- Get position if IPV4 delimiter
SET @colIndex = CHARINDEX( @delim, @ipAddress );


-- If not IPV4, then assume IPV6
IF @colIndex = 0
BEGIN
SELECT @delim = ':', @limit = 8, @colIndex = CHARINDEX( @delim, @ipAddress );


-- Get number of parts (delimiters)
WHILE @colIndex > 0
SELECT @parts += 1, @colIndex = CHARINDEX( @delim, @ipAddress, @colIndex + 1 );


SET @colIndex = CHARINDEX( @delim, @ipAddress );


IF @colIndex = 0
RETURN NULL;
END


-- Add trailing delimiter (need new variable of larger size)
SET @tIPAddress = @IPAddress + @delim;


WHILE @colIndex > 0
BEGIN
SET @token = SUBSTRING( @tIPAddress, @prevColIndex + 1, @Colindex - @prevColIndex - 1 );


IF @delim = ':'
BEGIN
SELECT @vbzone = CONVERT( VARBINARY(2), RIGHT( '0000' + @token, 4 ), 2 ), @vbytes += @vbzone;


-- Handles consecutive sections of zeros representation rule (i.e. ::)(https://en.wikipedia.org/wiki/IPv6#Address_representation)
IF @token = ''
WHILE @parts + 1 < @limit
SELECT @vbytes += @vbzone, @parts += 1;
END
ELSE
BEGIN
SELECT @vbzone = CONVERT( VARBINARY(1), CONVERT( TINYINT, @token )), @vbytes += @vbzone
END


SELECT @prevColIndex = @colIndex, @colIndex = CHARINDEX( @delim, @tIPAddress, @colIndex + 1 )
END


SET @bytes =
CASE @delim
WHEN ':' THEN @vbytes
ELSE /*0x000000000000000000000000 +*/ @vbytes -- Return IPV4 addresses as 4 byte binary (uncomment leading 0s section to force 16 byte binary)
END


RETURN @bytes


END

测试案例

Valid cases

SELECT dbo.IPAddressStringToVarbinary( '0000:0000:0000:0000:0000:0000:0000:0001' ) -- 0x0000000000000000000000000001 (check bug fix)
SELECT dbo.IPAddressStringToVarbinary( '0001:0002:0003:0040:0500:0600:7000:0089' ) -- 0x00010002000300400500060070000089
SELECT dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3:1319::370:7348' )     -- 0x20010DB885A308D31319000003707348 (check short hand)
SELECT dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3:1319:0000:370:7348' ) -- 0x20010DB885A308D31319000003707348
SELECT dbo.IPAddressStringToVarbinary( '192.168.1.72' ) -- 0xC0A80148
SELECT dbo.IPAddressStringToVarbinary( '127...1' ) -- 0x7F000001 (check short hand)
SELECT dbo.IPAddressStringToVarbinary( NULL ) -- NULL
SELECT dbo.IPAddressStringToVarbinary( '' ) -- NULL
-- Check that conversions return original address
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '0001:0002:0003:0040:0500:0600:7000:0089' )) -- '0001:0002:0003:0040:0500:0600:7000:0089'
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '127...1' )) -- 127.0.0.1
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '192.168.1.72' )) -- 192.168.1.72
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3:1319::370:7348' ))     -- 2001:0db8:85a3:08d3:1319:0000:0370:7348
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3:1314:0000:370:7348' )) -- 2001:0db8:85a3:08d3:1319:0000:0370:7348
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8:85a3:8d3::370:7348' )) -- 2001:0DB8:85A3:08D3:0000:0000:0370:7348
-- This is technically an invalid IPV6 (according to Wikipedia) but it parses correctly
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8::1319::370:7348' )) -- 2001:0DB8:0000:0000:1319:0000:0370:7348

无效案件

SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '2001:db8::1319::7348' )) -- 2001:0DB8:0000:0000:0000:1319:0000:7348 (ambiguous address)
SELECT dbo.IPAddressStringToVarbinary( '127.1' ) -- 127.0.0.1 (not supported short-hand)
SELECT dbo.IPAddressVarbinaryToString( dbo.IPAddressStringToVarbinary( '127.1' )) -- 127.0.0.1 (not supported short-hand)
SELECT dbo.IPAddressStringToVarbinary( '0300.0000.0002.0353' ) -- octal byte values
SELECT dbo.IPAddressStringToVarbinary( '0xC0.0x00.0x02.0xEB' ) -- hex values
SELECT dbo.IPAddressStringToVarbinary( 'C0.00.02.EB' ) -- hex values
小开

我们做了很多工作，我们需要找出哪些 IP 是在某些子网内。我发现最简单也是最可靠的方法是:

  1. 向每个表添加一个名为 IPInteger (bigint)的字段(如果无效，则设置 IP =’0.0.0.0’)
  2. For smaller tables, I use a trigger that updates IPInteger on change
  3. 对于较大的表，我使用 SPROC 刷新 IPInteger
    ALTER FUNCTION [dbo].[IP_To_INT ]
(
@IP CHAR(15)
)
RETURNS BIGINT
AS
BEGIN
DECLARE @IntAns BIGINT,
@block1 BIGINT,
@block2 BIGINT,
@block3 BIGINT,
@block4 BIGINT,
@base BIGINT
     

SELECT
@block1 = CONVERT(BIGINT, PARSENAME(@IP, 4)),
@block2 = CONVERT(BIGINT, PARSENAME(@IP, 3)),
@block3 = CONVERT(BIGINT, PARSENAME(@IP, 2)),
@block4 = CONVERT(BIGINT, PARSENAME(@IP, 1))
     

IF (@block1 BETWEEN 0 AND 255)
AND (@block2 BETWEEN 0 AND 255)
AND (@block3 BETWEEN 0 AND 255)
AND (@block4 BETWEEN 0 AND 255)
BEGIN
SET @base = CONVERT(BIGINT, @block1 * 16777216)
SET @IntAns = @base +
(@block2 * 65536) +
(@block3 * 256) +
(@block4)
END
ELSE
SET @IntAns = -1
RETURN @IntAns
END

提交答案