Solved

SQL query

Posted on 2014-09-22
22
125 Views
Last Modified: 2014-09-24
Hi Guys,
I have to kind of repost this case because I'm stock with this.
I have a table with a column containing IP ranges, some fields contains more than one IP range.
See below:
IP = 172.27.88.0/24
IP = 172.27.28.0/24 | IP = 172.26.28.0/25 | IP = 127.27.28.129/25
IP = 172.27.14.0/23
IP = 172.27.169.0/25
In total I have 45 rows and in total there is 58 IP ranges.
I want to insert the IP ranges into a temp table seperately so I will get 58 rows in total.
I also have another value from another column that I need to bring along as well.
It's on SQL 2005.
Thanks in advance
Rasmus
0
Comment
Question by:Coloplast
  • 11
  • 10
22 Comments
 

Author Comment

by:Coloplast
ID: 40336520
I forgot to mention that I want the ranges to be like 172.27.88.0/24 so the 'IP = ' and '|' stuff needs to filtered out.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40336564
I remember a very similar question. You didn't get the solution you wanted?
0
 

Author Comment

by:Coloplast
ID: 40336570
Kind of...I just can't get 58 rows, my query only gives me 45 rows, so I don't get the fields that contains multiple IP ranges into my temp table.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40336573
Can you post the query that you are using now?
0
 

Author Comment

by:Coloplast
ID: 40336590
Query:

CREATE TABLE #IP_Ranges(IPRange VARCHAR(18), IPLow VARCHAR(10), IPHigh VARCHAR(10))
DECLARE @id VARCHAR(200)
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT [selection_criteria] AS 'ID' 
    FROM [dbo].[egroup]
	WHERE [selection_criteria] LIKE 'IP%'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @id
WHILE @@FETCH_STATUS = 0 BEGIN
    
	DECLARE @IP_Row VARCHAR(200)
	DECLARE @pos SMALLINT
	SET @IP_Row = @id
	SET @IP_Row = REPLACE(@IP_Row,'IP = ', '')
	SET @pos = CHARINDEX('|',  @IP_Row)  
	WHILE @pos > 0
	BEGIN
	SET @IP_Row = SUBSTRING(@IP_Row, @pos+1, LEN(@IP_Row)-@pos)
	SET @pos  = CHARINDEX('|',  @IP_Row)  
	END
	
	DECLARE @CidrIP VARCHAR(200)
	SET @CidrIP = LTRIM(@IP_Row)
	
	INSERT INTO #IP_Ranges(IPRange, IPLow, IPHigh)
	VALUES(LTRIM(@IP_Row), dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)), dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)) + (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1))
FETCH NEXT FROM myCursor INTO @id
END
CLOSE myCursor
DEALLOCATE myCursor
SELECT * FROM #IP_Ranges
ORDER BY IPLow
DROP TABLE #IP_Ranges

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40336825
I have no idea what the computations on the IP address are doing.

The code below outputs the range as it displays in the original row.  I'm not sure what else "IP = 127.27.28.129/25" is supposed to translate to.

First create the split function below, as it is used by the main code.



SELECT 
    e.other_column_to_bring_along,
    IPLow,
    CASE WHEN CHARINDEX('/', ip_value) = 0 THEN IPLow
        ELSE PARSENAME(IPLow, 4) + '.' + PARSENAME(IPLow, 3) + '.' + PARSENAME(IPLow, 2) + '.' +
             + SUBSTRING(ip_value, CHARINDEX('/', ip_value + '/') + 1, 100) END AS IPHigh
    FROM (
        SELECT 
            1 AS other_column_to_bring_along,
            'IP = 172.27.28.0/24 | IP = 172.26.28.0/25 | IP = 127.27.28.129/25' AS [selection_criteria]
    ) AS e --[dbo].[egroup] e
    CROSS APPLY dbo.DelimitedSplit8K ( e.[selection_criteria], '|' ) AS ds
    CROSS APPLY ( SELECT LTRIM(RTRIM(REPLACE(REPLACE(ds.item, 'IP ', ''), '= ', ''))) AS ip_value ) AS assign_alias_1
    CROSS APPLY ( SELECT LEFT(ip_value, CHARINDEX('/', ip_value + '/') - 1) AS IPLow ) AS assign_alias_2
        WHERE e.[selection_criteria] LIKE 'IP%'

Open in new window

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = LTRIM(RTRIM(SUBSTRING(@pString, l.N1, l.L1)))
   FROM cteLen l
;

Open in new window

0
 

Author Comment

by:Coloplast
ID: 40338888
Hi Vitor, is there any hope?
0
 

Author Comment

by:Coloplast
ID: 40338893
Oh thanks, I will try it out ;-)
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40338898
Sorry Coloplast, I didn't have time to check this. I thought you are using a solution I gave, so would be more easy working with my code.
I know I participated in that question but couldn't found it. You have the link for that question? May helps Scott's also.
0
 

Author Comment

by:Coloplast
ID: 40338899
The IP range is translated into an IPLow value and an IPHigh value which makes me able to evaluate if an IP address is part of the IP range.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40338926
Ok, found it.

And the solution I gave would return the numbers like this:
 172.27.28.0/24
 172.26.28.0/25
 127.27.28.129/25
DECLARE @IP_Row VARCHAR(100)
DECLARE @pos SMALLINT

SET @IP_Row='IP = 172.27.28.0/24 | IP = 172.26.28.0/25 | IP = 127.27.28.129/25'
SET @IP_Row=REPLACE(@IP_Row,'IP = ', '')

SET @pos  = CHARINDEX('|',  @IP_Row )  
WHILE @pos > 0
BEGIN

  PRINT LTRIM(SUBSTRING(@IP_Row, 1, @pos-1))

  SET @IP_Row = SUBSTRING(@IP_Row, @pos+1, LEN(@IP_Row)-@pos)
  SET @pos  = CHARINDEX('|',  @IP_Row )  
END

PRINT LTRIM(@IP_Row)

Open in new window

I can see that you used this solution. Just wondering what the [ConvertIPToLong] function does?
0
 

Author Comment

by:Coloplast
ID: 40338948
ConvertIPToLong does the following:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ConvertIPToLong](@IP varchar(15))
RETURNS bigint
AS
BEGIN
    DECLARE @Long bigint
    SET @Long = CONVERT(bigint, PARSENAME(@IP, 4)) * 256 * 256 * 256 +
        CONVERT(bigint, PARSENAME(@IP, 3)) * 256 * 256 +
        CONVERT(bigint, PARSENAME(@IP, 2)) * 256 +
        CONVERT(bigint, PARSENAME(@IP, 1))

    RETURN (@Long)
END
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40338986
So, what you mean is when it finds a row like 'IP = 172.27.28.0/24 | IP = 172.26.28.0/25 | IP = 127.27.28.129/25' it only inserts the last IP (127.27.28.129/25 for this case) and you want to have the 3 IP's inserted, right?
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40338999
If so, then try this:
CREATE TABLE #IP_Ranges(IPRange VARCHAR(18), IPLow VARCHAR(10), IPHigh VARCHAR(10))
DECLARE @id VARCHAR(200)
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT [selection_criteria] AS 'ID' 
    FROM [dbo].[egroup]
	WHERE [selection_criteria] LIKE 'IP%'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @id
WHILE @@FETCH_STATUS = 0 BEGIN
    
	DECLARE @IP_Row VARCHAR(200)
	DECLARE @pos SMALLINT
	DECLARE @CidrIP VARCHAR(200)

	SET @IP_Row = @id
	SET @IP_Row = REPLACE(@IP_Row,'IP = ', '')
	SET @pos = CHARINDEX('|',  @IP_Row)  
	WHILE @pos > 0
	BEGIN
		SET @CidrIP = PRINT LTRIM(SUBSTRING(@IP_Row, 1, @pos-1))
		
		INSERT INTO #IP_Ranges(IPRange, IPLow, IPHigh)
		VALUES(LTRIM(@IP_Row), dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)), dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)) + (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1))
		
		SET @IP_Row = SUBSTRING(@IP_Row, @pos+1, LEN(@IP_Row)-@pos)
		SET @pos  = CHARINDEX('|',  @IP_Row)  
	END
	
	SET @CidrIP = PRINT LTRIM(SUBSTRING(@IP_Row, 1, @pos-1))
	
	INSERT INTO #IP_Ranges(IPRange, IPLow, IPHigh)
	VALUES(LTRIM(@IP_Row), dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)), dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)) + (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1))
FETCH NEXT FROM myCursor INTO @id
END
CLOSE myCursor
DEALLOCATE myCursor
SELECT * FROM #IP_Ranges
ORDER BY IPLow
DROP TABLE #IP_Ranges

Open in new window

0
 

Author Comment

by:Coloplast
ID: 40339013
Yes you are right.
When running you're query I get the following error:

Msg 156, Level 15, State 1, Line 64
Incorrect syntax near the keyword 'PRINT'.
Msg 156, Level 15, State 1, Line 73
Incorrect syntax near the keyword 'PRINT'.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40339027
Sorry, was from copy/paste. here you go:
CREATE TABLE #IP_Ranges(IPRange VARCHAR(18), IPLow VARCHAR(10), IPHigh VARCHAR(10))
DECLARE @id VARCHAR(200)
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT [selection_criteria] AS 'ID' 
    FROM [dbo].[egroup]
	WHERE [selection_criteria] LIKE 'IP%'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @id
WHILE @@FETCH_STATUS = 0 BEGIN
    
	DECLARE @IP_Row VARCHAR(200)
	DECLARE @pos SMALLINT
	DECLARE @CidrIP VARCHAR(200)

	SET @IP_Row = @id
	SET @IP_Row = REPLACE(@IP_Row,'IP = ', '')
	SET @pos = CHARINDEX('|',  @IP_Row)  
	WHILE @pos > 0
	BEGIN
		SET @CidrIP = LTRIM(SUBSTRING(@IP_Row, 1, @pos-1))
		
		INSERT INTO #IP_Ranges(IPRange, IPLow, IPHigh)
		VALUES(LTRIM(@IP_Row), dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)), dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)) + (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1))
		
		SET @IP_Row = SUBSTRING(@IP_Row, @pos+1, LEN(@IP_Row)-@pos)
		SET @pos  = CHARINDEX('|',  @IP_Row)  
	END
	
	SET @CidrIP = LTRIM(SUBSTRING(@IP_Row, 1, @pos-1))
	
	INSERT INTO #IP_Ranges(IPRange, IPLow, IPHigh)
	VALUES(LTRIM(@IP_Row), dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)), dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)) + (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1))
FETCH NEXT FROM myCursor INTO @id
END
CLOSE myCursor
DEALLOCATE myCursor
SELECT * FROM #IP_Ranges
ORDER BY IPLow
DROP TABLE #IP_Ranges

Open in new window

0
 

Author Comment

by:Coloplast
ID: 40339037
I get a lot of these errors:

(1 row(s) affected)
Msg 8152, Level 16, State 14, Line 66
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 66
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 66
String or binary data would be truncated.
The statement has been terminated.
Msg 536, Level 16, State 5, Line 73
Invalid length parameter passed to the SUBSTRING function.

(1 row(s) affected)
Msg 536, Level 16, State 5, Line 73
Invalid length parameter passed to the SUBSTRING function.

(1 row(s) affected)
Msg 536, Level 16, State 5, Line 73
Invalid length parameter passed to the SUBSTRING function.

(1 row(s) affected)
Msg 536, Level 16, State 5, Line 73
Invalid length parameter passed to the SUBSTRING function.

(1 row(s) affected)
Msg 536, Level 16, State 5, Line 73
Invalid length parameter passed to the SUBSTRING function.

(1 row(s) affected)
Msg 536, Level 16, State 5, Line 73
Invalid length parameter passed to the SUBSTRING function.

(1 row(s) affected)

(45 row(s) affected)
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40339429
I think I got it. Please try again with this code:
CREATE TABLE #IP_Ranges(IPRange VARCHAR(18), IPLow VARCHAR(10), IPHigh VARCHAR(10))
DECLARE @id VARCHAR(200)
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT [selection_criteria] AS 'ID' 
    FROM [dbo].[egroup]
	WHERE [selection_criteria] LIKE 'IP%'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @id
WHILE @@FETCH_STATUS = 0 BEGIN
    
	DECLARE @IP_Row VARCHAR(200)
	DECLARE @pos SMALLINT
	DECLARE @CidrIP VARCHAR(200)

	SET @IP_Row = @id
	SET @IP_Row = REPLACE(@IP_Row,'IP = ', '')
	SET @pos = CHARINDEX('|',  @IP_Row)  
	WHILE @pos > 0
	BEGIN
		SET @CidrIP = LTRIM(SUBSTRING(@IP_Row, 1, @pos-1))
		
		INSERT INTO #IP_Ranges(IPRange, IPLow, IPHigh)
		VALUES(@CidrIP, dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)), 
			dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)) + (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1))
		
		SET @IP_Row = SUBSTRING(@IP_Row, @pos+1, LEN(@IP_Row)-@pos)
		SET @pos  = CHARINDEX('|',  @IP_Row)  
	END
	
	SET @CidrIP = LTRIM(SUBSTRING(@IP_Row, 1, @pos-1))
	
	INSERT INTO #IP_Ranges(IPRange, IPLow, IPHigh)
	VALUES(@CidrIP, dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)), 
		dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)) + (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1))
FETCH NEXT FROM myCursor INTO @id
END
CLOSE myCursor
DEALLOCATE myCursor
SELECT * FROM #IP_Ranges
ORDER BY IPLow
DROP TABLE #IP_Ranges

Open in new window

0
 

Author Comment

by:Coloplast
ID: 40341167
The query gives 58 rows :-), but I still the same errors as last time.
0
 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40341174
It's bad to work without real data. Let's see if this now runs without errors:
CREATE TABLE #IP_Ranges(IPRange VARCHAR(18), IPLow VARCHAR(10), IPHigh VARCHAR(10))
DECLARE @id VARCHAR(200)
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT [selection_criteria] AS 'ID' 
    FROM [dbo].[egroup]
	WHERE [selection_criteria] LIKE 'IP%'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @id
WHILE @@FETCH_STATUS = 0 BEGIN
    
	DECLARE @IP_Row VARCHAR(200)
	DECLARE @pos SMALLINT
	DECLARE @CidrIP VARCHAR(200)

	SET @IP_Row = @id
	SET @IP_Row = REPLACE(@IP_Row,'IP = ', '')
	SET @pos = CHARINDEX('|',  @IP_Row)  
	WHILE @pos > 0
	BEGIN
		SET @CidrIP = LTRIM(SUBSTRING(@IP_Row, 1, @pos-1))
		
		INSERT INTO #IP_Ranges(IPRange, IPLow, IPHigh)
		VALUES(@CidrIP, dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)), 
			dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)) + (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1))
		
		SET @IP_Row = SUBSTRING(@IP_Row, @pos+1, LEN(@IP_Row)-@pos)
		SET @pos  = CHARINDEX('|',  @IP_Row)  
	END
	
	SET @CidrIP = @IP_Row
	
	INSERT INTO #IP_Ranges(IPRange, IPLow, IPHigh)
	VALUES(@CidrIP, dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)), 
		dbo.[ConvertIPToLong](left(@CidrIP, patindex('%/%' , @CidrIP) - 1)) & (cast(4294967295 as bigint) ^ (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1)) + (Power(2, 32 - Cast(substring(@CidrIP, patindex('%/%' , @CidrIP) + 1, 2) as int)) - 1))
FETCH NEXT FROM myCursor INTO @id
END
CLOSE myCursor
DEALLOCATE myCursor
SELECT * FROM #IP_Ranges
ORDER BY IPLow
DROP TABLE #IP_Ranges

Open in new window

0
 

Author Comment

by:Coloplast
ID: 40341287
It seems to be working now ;-)
I'm getting the data.
Thanks for your effort Vitor.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40341296
Great :)
You are welcome.

Cheers.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question