Access教程
  • sql server教程
  • MySQL教程 DB2教程 ORACLE教程
    当前位置:教程 > 数据库 > sql server教程 > 正文

    如何将作为字符串保存的IP地址变为二进制数值?

    编辑:教程网 来源:网络 时间:2011年07月03日 17:20
    对于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

  • 原文来自 教程网 转载请注明:http://www.websmill.com/shujuku/sql_server/8636.html

  • 上一篇:总结 SQL Server 中死锁产生的原因及解决办法
  • 下一篇:SQL SERVER应用问题解答13例(三)