Datatype for storing ip address in SQL Server

Question

What datatype should I choose for storing an IP Address in a SQL Server   By selecting the right datatype would it be easy enough to filter by IP address then

User · Answer

Here is some code to convert either IPV4 or IPv6 in varchar format to binary 16  and back  This is the smallest form I could think of  It should index well and provide a relatively easy way to filter on subnets  Requires SQL Server 2005 or later  Not sure it s totally bulletproof  Hope this helps   -- 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  gt  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  gt  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  lt   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  lt   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  lt   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

User · Answer

We do a lot of work where we need to figure out which IP s are within certain subnets  I ve found that the simplest and most reliable way to do this is   Add a field to each table called IPInteger  bigint   when invalid  set IP   0 0 0 0   For smaller tables  I use a trigger that updates IPInteger on change For larger tables  I use a SPROC to refresh the IPIntegers      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

User · Answer

sys dm exec connections uses varchar 48  after SQL Server 2005 SP1  Sounds good enough for me especially if you want to use it compare to your value   Realistically  you won t see IPv6 as mainstream for a while yet  so I d prefer the 4 tinyint route  Saying that  I m using varchar 48  because I have to use sys dm exec connections     Otherwise  Mark Redman s answer mentions a previous SO debate question

User · Answer

The following answer is based on answers by M  Turnhout and Jerry Birchler to this question but with the following improvements   Replaced the use of undocumented functions  sys fn varbintohexsubstring  fn varbintohexstr  with CONVERT   for binary styles Replaced XML  quot hacks quot    CAST    as xml  value  xs hexBinary      with CONVERT   for binary styles Fixed bug in Jerry Birchler s implementation of fn ConvertIpAddressToBinary  as pointed out by C Plock  Add minor syntax sugar  The code has been tested in SQL Server 2014 and SQL Server 2016  see test cases at the end  IPAddressVarbinaryToString Converts 4 bytes values to IPV4 and 16 byte values to IPV6 string representations  Note that this function does not shorten addresses  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  Test Cases  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  IPAddressStringToVarbinary Converts IPV4 and IPV6 string representations to 4 byte and 16 bytes binary values respectively  Note that this function is able to parse most  all of the commonly used  of shorthand address representations  e g  127   1 and 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  gt  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  gt  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  lt   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  Test Cases 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  Invalid cases 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

User · Answer

I like the functions of SandRock   But I found an error in the code of dbo fn ConvertIpAddressToBinary   The incoming parameter of  ipAddress VARCHAR 39  is too small when you concat the  delim to it   SET  ipAddress    ipAddress    delim   You can increase it to 40  Or better yet use a new variable that is bigger and use that internally   That way you don t lose the last pair on large numbers   SELECT dbo fn ConvertIpAddressToBinary  ffff ffff ffff ffff ffff ffff ffff ffff

User · Answer

I m using varchar 15  so far everything is working for me  Insert  Update  Select  I have just started an app that has IP Addresses  though I have not done much dev work yet   Here is the select statement   select   From dbo Server  where   IP      132 46 151 181   Go

User · Answer

As I want to handle both IPv4 and IPv6  I am using VARBINARY 16  and the following SQL CLR functions to convert the text IP address presentation to bytes and the reverse    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

User · Answer

I usually use a plain old VARCHAR filtering for an IPAddress works fine   If you want to filter on ranges of IP address I d break it into four integers

User · Answer

For people using  NET can use IPAddress class to parse IPv4 IPv6 string and store it as a VARBINARY 16   Can use the same class to convert byte   to string  If want to convert the VARBINARY in SQL   --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

User · Answer

Thanks RBarry   I m putting together an IP block allocation system and storing as binary is the only way to go     I m storing the CIDR representation  ex  192 168 1 0 24  of the IP block in a varchar field  and using 2 calculated fields to hold the binary form of the start and end of the block   From there  I can run fast queries to see if a given block as already been allocated or is free to assign     I modified your function to calculate the ending IP Address like so   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

User · Answer

You can use varchar  The length of IPv4 is static  but that of IPv6 may be highly variable   Unless you have a good reason to store it as binary  stick with a string  textual  type

User · Answer

The technically correct way to store IPv4 is binary 4   since that is what it actually is  no  not even an INT32 INT 4   the numeric textual form that we all know and love  255 255 255 255  being just the display conversion of its binary content    If you do it this way  you will want functions to convert to and from the textual-display format   Here s how to convert the textual display form to binary   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   And here s how to convert the binary back to the textual display form   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   Here s a demo of how to use them   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     UPDATE   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   Here s it in the example   SELECT bin FROM dbo fnBinaryIPv4  192 65 68 201   --should return 0xC04144C9 go   And here s how you would use it in an INSERT  INSERT INTo myIpTable SELECT  other column values               SELECT bin FROM dbo itvfBinaryIPv4  192 65 68 201

[sql-server] Datatype for storing ip address in SQL Server

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2005

Examples related to types

Examples related to ip-address