Jeff Moden (4/24/2015)
To be honest, the front end is storing the data incorrectly. The data should be sorted with just one IP address per row and, once the table is properly indexed, the lookups would be lightning fast and with no splitting needed. It would also make de-duping the data MUCH easier.
Further on Jeff's comment, storing the IP as integer and adding A,B,C class masks makes it much more efficient;-)
😎
Quick sample to play with
USE tempdb;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'dbo.ITVF_IP_NUMERICAL') IS NOT NULL DROP FUNCTION dbo.ITVF_IP_NUMERICAL;
EXEC (N'CREATE FUNCTION dbo.ITVF_IP_NUMERICAL
(
@IPADDR VARCHAR(15)
)
RETURNS TABLE
RETURN
/*
ITVF_IP_NUMERICAL
Converting an character representation of an IP address into
an integer and masking A,B,C classes
2015-04-26 Eirikur Eiriksson
*/
WITH IP_ADDRESS_DELIM AS
(
SELECT
CHARINDEX(CHAR(46),@IPADDR,1) AS POS_01
,CHARINDEX(CHAR(46),@IPADDR,CHARINDEX(CHAR(46),@IPADDR,1) + 1) AS POS_02
,CHARINDEX(CHAR(46),@IPADDR,CHARINDEX(CHAR(46),@IPADDR,CHARINDEX(CHAR(46),@IPADDR,1) + 1) + 1) AS POS_03
,LEN(@IPADDR) AS POS_04
)
,NUMERICAL_IP AS
(
SELECT
CONVERT(INT,
CONVERT(BINARY(1),CONVERT(TINYINT,SUBSTRING(@IPADDR,IAD.POS_03 + 1,(IAD.POS_04 -IAD.POS_03) ),0),1)
+ CONVERT(BINARY(1),CONVERT(TINYINT,SUBSTRING(@IPADDR,IAD.POS_02 + 1,(IAD.POS_03 -IAD.POS_02) -1),0),1)
+ CONVERT(BINARY(1),CONVERT(TINYINT,SUBSTRING(@IPADDR,IAD.POS_01 + 1,(IAD.POS_02 -IAD.POS_01) -1),0),1)
+ CONVERT(BINARY(1),CONVERT(TINYINT,SUBSTRING(@IPADDR,1,IAD.POS_01 -1) ,0),1)
) AS IP_INT
FROM IP_ADDRESS_DELIM IAD
)
SELECT
NI.IP_INT & 0xFF AS IP_AX
,NI.IP_INT & 0xFFFF AS IP_BX
,NI.IP_INT & 0xFFFFFF AS IP_CX
,NI.IP_INT AS IP_DX
FROM NUMERICAL_IP NI;
');
DECLARE @IP_LIST TABLE (IP_ID INT IDENTITY(1,1) NOT NULL,IPADDR VARCHAR(15) NOT NULL);
INSERT INTO @IP_LIST(IPADDR)
VALUES
('192.168.25.2')
,('192.168.25.22')
,('192.168.25.222')
,('192.168.25.2')
,('192.168.26.2')
,('192.168.27.2')
,('192.168.25.2')
,('192.169.25.2')
,('192.167.25.2')
,('192.168.25.2')
,('193.168.25.2')
,('194.168.25.2')
;
SELECT
IL.IP_ID
,IL.IPADDR
,X.IP_AX
,X.IP_BX
,X.IP_CX
,X.IP_DX
FROM @IP_LIST IL
CROSS APPLY dbo.ITVF_IP_NUMERICAL(IL.IPADDR) AS X;
Sample output
IP_ID IPADDR IP_AX IP_BX IP_CX IP_DX
----------- --------------- ----------- ----------- ----------- -----------
1 192.168.25.2 192 43200 1681600 35236032
2 192.168.25.22 192 43200 1681600 370780352
3 192.168.25.222 192 43200 1681600 -568743744
4 192.168.25.2 192 43200 1681600 35236032
5 192.168.26.2 192 43200 1747136 35301568
6 192.168.27.2 192 43200 1812672 35367104
7 192.168.25.2 192 43200 1681600 35236032
8 192.169.25.2 192 43456 1681856 35236288
9 192.167.25.2 192 42944 1681344 35235776
10 192.168.25.2 192 43200 1681600 35236032
11 193.168.25.2 193 43201 1681601 35236033
12 194.168.25.2 194 43202 1681602 35236034