Coloplast
asked on
SQL query
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
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
I remember a very similar question. You didn't get the solution you wanted?
ASKER
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.
Can you post the query that you are using now?
ASKER
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
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.
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%'
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
;
ASKER
Hi Vitor, is there any hope?
ASKER
Oh thanks, I will try it out ;-)
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.
I know I participated in that question but couldn't found it. You have the link for that question? May helps Scott's also.
ASKER
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.
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
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)
I can see that you used this solution. Just wondering what the [ConvertIPToLong] function does?
ASKER
ConvertIPToLong does the following:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ConvertIPToLong](@I P 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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ConvertIPToLong](@I
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
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?
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
ASKER
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'.
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'.
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
ASKER
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)
(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)
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
ASKER
The query gives 58 rows :-), but I still the same errors as last time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It seems to be working now ;-)
I'm getting the data.
Thanks for your effort Vitor.
I'm getting the data.
Thanks for your effort Vitor.
Great :)
You are welcome.
Cheers.
You are welcome.
Cheers.
ASKER