How to join SQL tables where value is Between min and max value

join;

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.

Best Answer

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
Related Question