May 29, 2015 at 11:44 am
Hi,
I am trying to grab the IP address from a field and having trouble getting some IP addresses. The query is below
SELECT REPLACE(LEFT(IPAddress0, LEN('%.%.%.%.%.%.%') - PATINDEX(',', IPAddress0)), ',', '') AS IPAddress, SysName0, SystemID
FROM SystemsRE
Example that works:
132.135.3.100, fe90::bu54:e9z5:61a9:5003
Results in 132.135.3.100
Example that doesn't work:
132.235.219.113, fe80::28d9:l69a:5bbd:gr2f
Results in 132.235.219.1
Now if I add .% to LEN('%.%.%.%.%.%.%.%') is works, but then on shorter IP addresses some IPs get a trailing letter 132.236.70.3f - the f coming from the MAC access portion
May 29, 2015 at 11:47 am
TJT (5/29/2015)
Hi,I am trying to grab the IP address from a field and having trouble getting some IP addresses. The query is below
SELECT REPLACE(LEFT(IPAddress0, LEN('%.%.%.%.%.%.%') - PATINDEX(',', IPAddress0)), ',', '') AS IPAddress, SysName0, SystemID
FROM SystemsRE
Example that works:
132.135.3.100, fe90::bu54:e9z5:61a9:5003
Results in 132.135.3.100
Example that doesn't work:
132.235.219.113, fe80::28d9:l69a:5bbd:gr2f
Results in 132.235.219.1
Now if I add .% to LEN('%.%.%.%.%.%.%.%') is works, but then on shorter IP addresses some IPs get a trailing letter 132.236.70.3f - the f coming from the MAC access portion
One, is the IP address always the first value in the list. Two, is it always terminated by a comma (,). Three, are you trying to extract just the IP address?
May 29, 2015 at 11:49 am
Correct, the IP Address is always first and ends with a comma
May 29, 2015 at 11:55 am
TJT (5/29/2015)
Correct, the IP Address is always first and ends with a comma
select left(@TestStr,patindex('%,%',@TestStr) - 1);
I can't post the declare of the @TestStr with the ip and mac address as a string value. Has to be web filtering at work.
May 29, 2015 at 12:01 pm
Lynn Pettis (5/29/2015)
TJT (5/29/2015)
Correct, the IP Address is always first and ends with a comma
select left(@TestStr,patindex('%,%',@TestStr) - 1);
I can't post the declare of the @TestStr with the ip and mac address as a string value. Has to be web filtering at work.
Lynn, stop looking over my shoulder! 😀
I was just about to post something very similar.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 29, 2015 at 12:03 pm
OK, you mean something like this?
DECLARE (at)TestStr AS VARCHAR(20)
select left(@TestStr,patindex('%,%',@TestStr) - 1);
May 29, 2015 at 12:13 pm
How about:
DECLARE @SomeString VARCHAR(50)
SET @SomeString = '132.135.3.100, fe90::bu54:e9z5:61a9:5003'
SELECT LEFT(@SomeString, CHARINDEX(',', @SomeString)-1) AS 127.0.0.1
, SUBSTRING(@SomeString, CHARINDEX(',', @SomeString) + 2, 25) AS [MAC]
SET @SomeString = '132.235.219.113, fe80::28d9:l69a:5bbd:gr2f'
SELECT LEFT(@SomeString, CHARINDEX(',', @SomeString)-1)AS 127.0.0.1
, SUBSTRING(@SomeString, CHARINDEX(',', @SomeString) + 2, 25) AS [MAC]
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 29, 2015 at 12:15 pm
Does the string only have 'IPAddress, MACAddress'?
May 29, 2015 at 12:17 pm
Quick suggestion, use dbo.DelimitedSplit8K on the comma
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(IP_ADDR) AS
( SELECT * FROM (VALUES
('132.135.3.100, fe90::bu54:e9z5:61a9:5003')
,('132.235.219.113, fe80::28d9:l69a:5bbd:gr2f')
) AS X(IP_ADDR)
)
SELECT
SP.Item
FROM SAMPLE_DATA SD
CROSS APPLY dbo.DelimitedSplit8K(SD.IP_ADDR,',') AS SP
WHERE SP.ItemNumber = 1;
Results
Item
----------------
132.135.3.100
132.235.219.113
May 29, 2015 at 12:19 pm
Yes the string only has IP and MAC address.
The examples you provided do work with a string. I need to do this for each row of the field returned from the database. Each row looks like '132.135.3.100, fe90::bu54:e9z5:61a9:5003'
BTW ... these are made up IP address and MAC addresses
DECLARE @SomeString VARCHAR(50)
SET @SomeString = '132.135.3.100, fe90::bu54:e9z5:61a9:5003'
SELECT LEFT(@SomeString, CHARINDEX(',', @SomeString)-1) AS 127.0.0.1
, SUBSTRING(@SomeString, CHARINDEX(',', @SomeString) + 2, 25) AS [MAC]
May 29, 2015 at 12:21 pm
Eirikur Eiriksson (5/29/2015)
Quick suggestion, use dbo.DelimitedSplit8K on the comma😎
USE tempdb;GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(IP_ADDR) AS
( SELECT * FROM (VALUES
('132.135.3.100, fe90::bu54:e9z5:61a9:5003')
,('132.235.219.113, fe80::28d9:l69a:5bbd:gr2f')
) AS X(IP_ADDR)
)
SELECT
SP.Item
FROM SAMPLE_DATA SD
CROSS APPLY dbo.DelimitedSplit8K(SD.IP_ADDR,',') AS SP
WHERE SP.ItemNumber = 1;
Results
Item----------------
132.135.3.100
132.235.219.113
< friendly rant >
Eirikur, really, ;; and then the WITH for the CTE?? Semicolons are terminators not begininators. Microsoft documentation in BOL is WRONG to start CTEs with a semicolon. The semicolon needs to be at the end of the previous statement.
< /Friendly rant >
May 29, 2015 at 12:32 pm
Taking Eirikur's set up:
SET NOCOUNT ON;
WITH SAMPLE_DATA(IP_ADDR) AS
( SELECT * FROM (VALUES
('132.135.3.100, fe90::bu54:e9z5:61a9:5003')
,('132.235.219.113, fe80::28d9:l69a:5bbd:gr2f')
) AS X(IP_ADDR)
)
SELECT
IP,
MAC
FROM
SAMPLE_DATA sd
CROSS APPLY (SELECT LEFT(IP_ADDR, PATINDEX('%,%', IP_ADDR) - 1)) ca1(IP)
CROSS APPLY (SELECT RIGHT(IP_ADDR, LEN(IP_ADDR) - PATINDEX('%,%', IP_ADDR) - 1)) ca2(MAC);
GO
May 29, 2015 at 12:33 pm
OK I tried the example below and I get 'Invalid object name 'dbo.DelimitedSplit8k''
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(IP_ADDR) AS
( SELECT * FROM (VALUES
('132.135.3.100, fe90::bu54:e9z5:61a9:5003')
,('132.235.219.113, fe80::28d9:l69a:5bbd:gr2f')
) AS X(IP_ADDR)
)
SELECT
SP.Item
FROM SAMPLE_DATA SD
CROSS APPLY dbo.DelimitedSplit8K(SD.IP_ADDR,',') AS SP
WHERE SP.ItemNumber = 1;
May 29, 2015 at 12:37 pm
TJT (5/29/2015)
OK I tried the example below and I get 'Invalid object name 'dbo.DelimitedSplit8k''USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(IP_ADDR) AS
( SELECT * FROM (VALUES
('132.135.3.100, fe90::bu54:e9z5:61a9:5003')
,('132.235.219.113, fe80::28d9:l69a:5bbd:gr2f')
) AS X(IP_ADDR)
)
SELECT
SP.Item
FROM SAMPLE_DATA SD
CROSS APPLY dbo.DelimitedSplit8K(SD.IP_ADDR,',') AS SP
WHERE SP.ItemNumber = 1;
Sorry, my bad, you'll find the function's source here[/url]
😎
May 29, 2015 at 12:39 pm
Lynn Pettis (5/29/2015)
< friendly rant >
Eirikur, really, ;; and then the WITH for the CTE?? Semicolons are terminators not begininators. Microsoft documentation in BOL is WRONG to start CTEs with a semicolon. The semicolon needs to be at the end of the previous statement.
< /Friendly rant >
I did hesitate for a fraction of a second, shall or shan't I add the Begininator:-D
😎
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply