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;
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy