How to convert varchar iPv4 Address into a number deterministically

Question

What are the various ways to convert a varchar iPv4 Address into a number without using PARSENAME() (non-deterministic) function in SQL Server, in order to create a persisted calculated column?

I used the string manipulation technique from Extract part of string based on nth instance of character to create the following:

Query

select 
((256*256*256)*convert(bigint, ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),1, 9)))))
+
((256*256)*convert(bigint,ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),10, 9)))))
+
((256)*convert(bigint,ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),19, 9)))))
+
(convert(bigint,ltrim(rtrim(substring(replace('255.255.255.255','.',replicate(' ',8)),28, 9)))))

Here is a visual of what the string manipulation looks like before each segment is converted to BIGINT and multiplied:

... This works to create a deterministic calculated column that can be persisted. What other more efficient techniques are there out there?

Answer 1

One option is to use SQLCLR. It is rather easy to create a method to split the string on the periods, convert each to an Int64, and multiply by the appropriate power of 256. Be sure to set IsDeterministic=true in the SqlFunction attribute, which will not only allow it to be persisted, but will also allow it to participate in parallel plans :-). And in fact, the SQL# SQLCLR library (that I created) contains functions (which are in the Free version) to do this conversion: INET_AddressToNumber and INET_NumberToAddress.

Test Setup:

CREATE TABLE #TempAddresses (IPAddress VARCHAR(15) NULL);

-- populate 1 million rows, with a NULL every 1000 rows
;WITH cte AS
(
  SELECT  TOP (1000000) ac1.column_id,
          ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [RowNum]
  FROM    master.sys.all_columns ac1
  CROSS APPLY  master.sys.all_columns ac2
)
INSERT INTO #TempAddresses ([IPAddress])
SELECT CASE cte.[RowNum] % 1000
         WHEN 0 THEN NULL
         ELSE CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
              + '.' +
              CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
              + '.' +
              CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
              + '.' +
              CONVERT(VARCHAR(3), CONVERT(INT, CRYPT_GEN_RANDOM(1)))
       END
FROM   cte

Regarding the following three tests, please note:

• They are shown in order of fastest to slowest.
• I ran each SELECT several times and kept the best time for each, not average time.

SQLCLR Test and Results:

DECLARE @Test1 BIGINT;
SET STATISTICS TIME ON;
SELECT @Test1 =  -- Comment out "@Test1 = " to test returning the value
       SQL#.INET_AddressToNumber(CONVERT(NVARCHAR(15), tmp.[IPAddress]))
FROM   #TempAddresses tmp;
SET STATISTICS TIME OFF;

-- CPU time = 2454 ms,  elapsed time = 5133 ms.
-- CPU time = 1594 ms,  elapsed time = 1617 ms. (into variable)

MartinSmith's Test and Results:

DECLARE @Test3 BIGINT;
SET STATISTICS TIME ON;
SELECT @Test3 =  -- Comment out "@Test3 = " to test returning the value
         256 * 256 * 256 * CAST(FLOOR(LEFT(tmp.[IPAddress],3)) AS BIGINT)
       +       256 * 256 * CAST(FLOOR(SUBSTRING(tmp.[IPAddress],1 + CHARINDEX('.', tmp.[IPAddress]),3)) AS BIGINT)
       +             256 * SUBSTRING(REPLACE(tmp.[IPAddress],'.',SPACE(8)),19, 9)
       +                   RIGHT(tmp.[IPAddress], -1 + CHARINDEX('.', REVERSE(tmp.[IPAddress])))
FROM   #TempAddresses tmp;
SET STATISTICS TIME OFF;

-- CPU time = 2781 ms,  elapsed time = 5151 ms.
-- CPU time = 2156 ms,  elapsed time = 2156 ms. (into variable)

O.P.'s Test and Results:

DECLARE @Test2 BIGINT;
SET STATISTICS TIME ON;
SELECT @Test2 =  -- Comment out "@Test2 = " to test returning the value
       ((256*256*256) * CONVERT(BIGINT, LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),1, 9)))))
       + ((256*256)*CONVERT(BIGINT,LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),10, 9)))))
       + ((256)*CONVERT(BIGINT,LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),19, 9)))))
       + (CONVERT(BIGINT,LTRIM(RTRIM(SUBSTRING(REPLACE(tmp.[IPAddress],'.',REPLICATE(' ',8)),28, 9)))))
FROM   #TempAddresses tmp;
SET STATISTICS TIME OFF;

-- CPU time = 3531 ms,  elapsed time = 5249 ms.
-- CPU time = 2515 ms,  elapsed time = 2534 ms. (into variable)

One thing to keep in mind regarding the SQLCLR option: using a SQLCLR function in a computed column is a dependency that will prevent the Assembly containing the function from being dropped. If you are able to use ALTER ASSEMBLY, then you won't being dropping and re-creating the Assembly. But ALTER ASSEMBLY is only allowed if there are no new or removed methods, and if the signatures of existing methods are still the same. If any of those changes are being made, then you will have to drop the Assembly and then create it again. And that will require that you first drop the computed column that is referencing the SQLCLR function contained in that Assembly. This might make the SQLCLR option less desirable in the use case of placing a function in a computed column, whereas if the function was in a query, Stored Procedure, View, etc, then this wouldn't be a concern.

It is possible to wrap the SQLCLR function in a T-SQL function and reference the T-SQL function in the computed column, but then you lose the benefit of SQLCLR functions being able to participate a parallel execution plan.

Answer 2

Another alternative would be the following.

The number of CHARINDEX operations is kept somewhat manageable by not attempting to find the start and end position for each octet. It just finds the start position and extracts the next three characters then uses FLOOR to discard any dot (and any subsequent character to that.)

The third octet was still pretty messy though so I used the approach in your question for that with a minor simplification that replicate(' ',8) can be replaced with space(8)

CREATE TABLE Ip4Address
(
Dotted VARCHAR(15) CHECK (Dotted NOT LIKE '%[^.0-9]%' COLLATE Latin1_General_100_BIN2 /*Only dots and digits*/
                                 AND Dotted NOT LIKE '%..%'          /*No sequences of 2 or more dots*/
                                 AND Dotted NOT LIKE '%[0-9][0-9][0-9][0-9]%'          /*No sequences of 4 or more digits*/
                                 AND LEN(Dotted) - LEN(REPLACE(Dotted, '.', '')) = 3), /*exactly 3 dots*/

Integer AS   256 * 256 * 256 * CAST(FLOOR(LEFT(Dotted,3)) AS BIGINT)
           +       256 * 256 * CAST(FLOOR(SUBSTRING(Dotted,1 + CHARINDEX('.', Dotted),3)) AS BIGINT)
           +             256 * SUBSTRING(REPLACE(Dotted,'.',SPACE(8)),19, 9)
           +                   RIGHT(Dotted, -1 + CHARINDEX('.', REVERSE(Dotted))) PERSISTED
)

Is this method more efficient?

Possibly.

A quick test showed the following

+---------------------------------------------------+-------------------+--------------------------------+
|                      Action                       | Time Elapsed (ms) | Time Elapsed per address ( μs) |
+---------------------------------------------------+-------------------+--------------------------------+
| Generating 10 million dotted IP addreses          |             37586 |                         3.7586 |
| Generating 10 m and running my calculation        |            106106 |                        10.6106 |
| Generating 10 m and running original  calculation |            119732 |                        11.9732 |
+---------------------------------------------------+-------------------+--------------------------------+

so it averaged 1.36 microseconds faster per row in my test so it seems like a micro optimisation not worth spending time on IMO.

Regarding efficiency you could consider not persisting it at all. It doesn't need to be PERSISTED to be indexed. Just deterministic. The index does of course persist it anyway but it won't bloat up the row size in the data pages.

---

Test script used

WITH
  L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2^1
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),       -- 2^2
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),       -- 2^4
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),       -- 2^8
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L3),
  Dotted AS (select  TOP 10000000  CONCAT(N1.N,'.',N2.N,'.',N3.N,'.',N4.N) AS Dotted
from nums N1, nums N2, nums N3, nums N4)
/*
Query 1 - baseline 
SELECT MAX(Dotted) FROM Dotted
*/
/*
Query 2 
SELECT MAX(
256 * 256 * 256 * CAST(FLOOR(LEFT(Dotted,3)) AS BIGINT)
           +       256 * 256 * CAST(FLOOR(SUBSTRING(Dotted,1 + CHARINDEX('.', Dotted),3)) AS BIGINT)
           +             256 * SUBSTRING(REPLACE(Dotted,'.',SPACE(8)),19, 9)
           +                   RIGHT(Dotted, -1 + CHARINDEX('.', REVERSE(Dotted))) 
)
FROM Dotted
*/
/*
Query 3
SELECT MAX(((256*256*256)*convert(bigint, ltrim(rtrim(substring(replace(Dotted,'.',replicate(' ',8)),1, 9)))))
+
((256*256)*convert(bigint,ltrim(rtrim(substring(replace(Dotted,'.',replicate(' ',8)),10, 9)))))
+
((256)*convert(bigint,ltrim(rtrim(substring(replace(Dotted,'.',replicate(' ',8)),19, 9)))))
+
(convert(bigint,ltrim(rtrim(substring(replace(Dotted,'.',replicate(' ',8)),28, 9))))))
FROM Dotted  --2:07 4295072150
*/

Answer 3

Given the constraints you're under with the computed column, that's probably the best you'll do. Constructing the same function using CHARINDEX and SUBSTRING would be even uglier IMO.

Be sure to leave a comment in your version control system for any future maintainers. That's not the most obvious way of splitting a string.

Answer 4

Consider Using Calculated Field Within a Table

I don't know if this is the most efficient, but it sure was fun to write and I can't imagine writing it being any easier.

Here I have the table tbl_IPv4 do all the work of making the conversion via a calculated field. I simply transformed the IP address into a select statement so it could be used to insert into the tbl_IPv4 table by (add a SELECT/change periods to commas). Sooo... if you're inserting IP addresses and need an on-the-fly conversion without a nightmarish calculated field--this might work for you. Note, the 256.0 is carefully placed in the first octet so SQL won't return an arithmetic overflow error.

CREATE TABLE [dbo].[tbl_IPv4](
    [oct1] [char](8) NULL,
    [oct2] [char](8) NULL,
    [oct3] [char](8) NULL,
    [oct4] [char](8) NULL,
    [IPv4Total]  AS ((((((256.0)*(256))*(256))*[oct1]+((256)*(256))*[oct2])+(256)*[oct3])+[oct4]) PERSISTED
) 


    declare @ipv4 varchar(255)= '255.255.255.255'
    set @ipv4 = 'select ' + replace(@ipv4, '.',',')
    insert into tbl_IPv4
    exec (@ipv4)
    select * from tbl_ipv4

I used PERSISTED to stop calculating and store those values instead and even index them. It even shows that this method is deterministic.

Per Microsoft:

PERSISTED Specifies that the Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated. Marking a computed column as PERSISTED allows an index to be created on a computed column that is deterministic, but not precise. For more information, see Indexes on Computed Columns. Any computed columns used as partitioning columns of a partitioned table must be explicitly marked PERSISTED. computed_column_expression must be deterministic when PERSISTED is specified.

p.s. SQL Server delightfully added all the parenthesis and brackets to my calculated field. You can test the calculated field as follows:

select 256.0*256*256*oct1 +256*256*oct2 + 256*oct3 + oct4 from tbl_ipv4 

And perhaps for even less calculations and less readability you could write or use in the calculated field in tbl_IPv4:

select 16777216.0*oct1 +65536*oct2 + 256*oct3 + oct4 from tbl_ipv4 

Answer 5

The newly imported table contains IP addresses in a numerical format, which is different from the more commonly used string format. The best way to convert the string representation of an IP address to a numeric value is to write a function in SQL Server. The following script shows a function that accomplishes this task. -- Converts a string based IP to Integer -- For example: 192.168.1.200 --> 3232235976

create function IP2INT(@ipAddress varchar(20)) RETURNS bigint AS BEGIN

DECLARE @FirstDot int, @SecondDot int, @ThirdDot int,
        @FirstOctet varchar(3), @SecondOctet varchar(3),
        @ThirdOctet varchar(3), @FourthOctet varchar(3),
        @Result bigint

    SET @FirstDot = CHARINDEX('.', @ipAddress)
    SET @SecondDot = CHARINDEX('.', @ipAddress, @FirstDot + 1)
    SET @ThirdDot = CHARINDEX('.', @ipAddress, @SecondDot + 1)

    SET @FirstOctet = SUBSTRING(@ipAddress, 1, @FirstDot - 1)
    SET @SecondOctet = SUBSTRING(@ipAddress, @FirstDot + 1, @SecondDot - @FirstDot - 1)
    SET @ThirdOctet = SUBSTRING(@ipAddress, @SecondDot + 1, @ThirdDot - @SecondDot - 1)
    SET @FourthOctet = SUBSTRING(@ipAddress, @ThirdDot + 1, 3)

    SET @Result = 16777216 * CAST(@FirstOctet as bigint) +
                  65536    * CAST(@SecondOctet as bigint) +
                  256      * CAST(@ThirdOctet as bigint) +
                             CAST(@FourthOctet as bigint)
    RETURN(@Result);
 END

Fetching data from the database The easiest way to fetch the location of an IP address is the write the following query:

-- Simple but not very efficient query. May take a long -- time to run.

select *
from ip2Location
where dbo.IP2INT('209.178.205.30') between IPFrom and IPTo

There is a significant problem with this query: it runs very slow. The machine we ran this query on took about almost 1 minute to run. This is because the table contains almost 3 million entries and the SELECT query above does not use any index. In fact, it cannot use any index.

Source: aboutmyip.com/AboutMyXApp/IP2Location.jsp