1

I am currently looking to join two tables together based on an IP and IP range. Both the IP and Range are currently stored as BIGINT.

For example we have a table MasterData with IPs in integer format [IPInteger] and we have another table called IPInformation which has all IP data but also has [start_ip_int] and [end_ip_int] range.

What I would like to to is join all the data in MasterData and All the data in the IPInformation based on the IPInteger being within the range of [start_ip_int] and [end_ip_int] on the IPInformation table.

Thanks in advance for any assistance you can provide.

2
  • 1
    Not sure I understand the problem, do you want a join between the tables in question using ON x.IPInteger between y.start_ip_int and y.end_ip_int?
    – Lennart
    Apr 10 '15 at 11:13
  • @Lennart yes thats correct.
    – Jonathan
    Apr 10 '15 at 11:44
0

Following is the SQL Server version in steps to work on IPs Range this might help you to convert in SqL

Step 1: Create function [usp_stringpart] in your Database

Create By: Aasim Abdullah
Description: Function takes a string and section number of string which
is required and separator, which separates different
sections of given string

Create FUNCTION [dbo].[usp_stringpart]
    (
      @InputString NVARCHAR(1000),
      @Section SMALLINT,
      @Separator NCHAR(1)
    )
RETURNS NVARCHAR(1000)
    BEGIN
        DECLARE @StartPosition INT,
            @EndPosition INT,
            @Cycle INT,
            @ResultString NVARCHAR(1000)
        SELECT  @Cycle = 0, @StartPosition = 0
        WHILE @Cycle < @Section - 1
            BEGIN
                SELECT  @StartPosition = CHARINDEX(@Separator, @InputString,
                                                   @StartPosition) + 1
                IF @StartPosition = 1 
                    SELECT  @Cycle = @Section
                ELSE 
                    SELECT  @Cycle = @Cycle + 1
            END
        SELECT  @EndPosition = CHARINDEX(@Separator, @InputString,
                                         @StartPosition)
        SELECT  @ResultString = LTRIM(RTRIM(SUBSTRING(@InputString,
                                                      @StartPosition,
                                                      CASE @EndPosition
                                                        WHEN 0
                                                        THEN ( LEN(@InputString) + 1 )
                                                             - @StartPosition
                                                        ELSE ( @EndPosition - @StartPosition )
                                                      END)))
        RETURN @ResultString
    END

Step 2: Add Column to save integer value of IPs

ALTER TABLE TableContainIPs ADD IPIntegers BIGINT 

Step 3: Convert IPs to Integers

UPDATE TableContainIPs
SET  IPIntegers  = CAST(
                        (CAST( TableContainIPs.dbo.usp_stringpart(REPLACE(IP,',',''),1,'.')AS BIGINT) *100000000000) 
                        +(CAST(TableContainIPs.dbo.usp_stringpart(REPLACE(IP,',',''),2,'.') AS BIGINT) *100000000)
                        +(CAST(TableContainIPs.dbo.usp_stringpart(REPLACE(IP,',',''),3,'.') AS BIGINT) *100000)
                        +(CAST(TableContainIPs.dbo.usp_stringpart(REPLACE(IP,',',''),4,'.')AS BIGINT) *100)
                       AS BIGINT)
 FROM TableContainIPs

Step 4: Get IPs

select P.IP, S.*
FROM  TableContainIPs P 
inner JOIN IPLibrary S  
ON P.IPIntegers BETWEEN s.IPasINTFrom AND s.IPasINTTo
1
  • This is great, thanks for your help, works like a charm
    – Jonathan
    Apr 10 '15 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.