我们使用二进制(16)字段存储IP地址。我们这样做是因为它可以保存IPv4和IPv6地址,并且与.Net IPAddress类轻松使用。
但是,我创建了以下SQL函数,将二进制地址转换为用于报告目的的IP地址字符串。
CREATE FUNCTION fn_ConvertBinaryIPAddressToString
(
@binaryIP binary(16)
)
RETURNS nvarchar(39)
AS
BEGIN
DECLARE @ipAsString nvarchar(39)
-- Is IPv4
IF (substring(@binaryIP, 5, 1) = 0x00) <-- Is there a better way?
BEGIN
SELECT @ipAsString = CAST(CAST(substring(@binaryIP, 1, 1) AS int) AS nvarchar(3)) + '.' +
CAST(CAST(substring(@binaryIP, 2, 1) AS int) AS nvarchar(3)) + '.' +
CAST(CAST(substring(@binaryIP, 3, 1) AS int) AS nvarchar(3)) + '.' +
CAST(CAST(substring(@binaryIP, 4, 1) AS int) AS nvarchar(3))
END
ELSE
BEGIN
-- Is IPv6
-- taken and modified from http://support.microsoft.com/kb/104829
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @ipAsString = ''
SELECT @i = 1
SELECT @length = 16
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = convert(int, substring(@binaryIP,@i,1))
SELECT @firstint = floor(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @ipAsString = @ipAsString + substring(@hexstring, @firstint+1, 1) + substring(@hexstring, @secondint+1, 1)
IF (@i % 2 = 0)
SELECT @ipAsString = @ipAsString + ':'
SELECT @i = @i + 1
END
END
-- Return the result of the function
RETURN @ipAsString
END
GO
目前,如果第5个字节为0,则假定它是IPv4地址。这是一个安全的假设吗?检查剩余的所有字节是否为零更好,还是有更好的方法?
编辑: 删除不必要的转换。