March 24, 2013 at 12:41 pm
Hi SQL Gurus,
I need some help .. I want only the last part of IP from the string.For example :-
jdbc:inetpool:inetdae:10.25.81.123:17100?database=us_nnnn
i need "123" .
The ip address will be dynamic/change.
Any help will be highly appreciated.
Thanks,
Viv
Roshan
March 24, 2013 at 2:17 pm
There are a ton of ways to do this but they all usually depend on certain things being there unless you happen to be pretty good at writing AI code. 😛
Assuming that the only periods in the whole string will be a part of the IP address and the IP address will always be immediately followed by a colon, this is one of the simplest methods.
--===== Setup the test. This is NOT a part of the solution.
DECLARE @SomeString VARCHAR(8000);
SELECT @SomeString = 'jdbc:inetpool:inetdae:10.25.81.123:17100?database=us_nnnn';
--===== Demonstrate the solution which could be modified to work on a table column.
SELECT SUBSTRING(PARSENAME(@SomeString,1),1,CHARINDEX(':',PARSENAME(@SomeString,1))-1);
--Jeff Moden
Change is inevitable... Change for the better is not.
March 24, 2013 at 8:16 pm
I haven't tested any of these options for performance. On a small data set all of them will probably perform OK. But if you are going to use this in production or on a very large table then it would be necessary to create some performance metrics against the real data.
--some options
;WITH sampledata
AS
(
SELECT * FROM
(VALUES
('jdbc:inetpool:inetdae:10.25.81.123:17100?database=us_nnnn'),
('jdbc:inetpool:inetdae:10.25.81.456:17100?database=us_nnnn'),
('jdbc:inetpool:inetdae:10.25.81.789:17100?database=us_nnnn'),
('jdbc:inetpool:inetdae:10.25.81.012:17100?database=us_nnnn'),
('jdbc:inetpool:inetdae:10.25.81.321:17100?database=us_nnnn')
) AS data(127.0.0.1)
)
SELECT
--if the IP part 4 is always 3 digits and no periods in the text following the IP
SUBSTRING(IP,LEN(IP)+2-CHARINDEX('.',REVERSE(IP)),3) AS [IP4-1]
--if everything before and after the IP is always the same
,STUFF(REVERSE(STUFF(REVERSE(IP),1,23,'')),1,31,'') AS [IP4-2]
--will find the IP part even if the other text changes assuming just the 3 periods in the IP
,STUFF(REVERSE(STUFF(REVERSE(IP),1,CHARINDEX(':',REVERSE(IP)),'')),1,CHARINDEX('.',IP,(CHARINDEX('.',IP,CHARINDEX('.',IP,(CHARINDEX('.',IP)+1)))+1)),'') AS [IP4-3]
FROM
sampledata
--this will work even if the other text is variable.
--assumes the only periods are in the IP address...if not just change
--the itemnumber in the where clause (see second example)
;WITH sampledata
AS
(
SELECT * FROM
(VALUES
('jdbc:inetpool:inetdae:10.25.81.123:17100?database=us_nnnn'),
('jdbc:inetpool:inetdae:10.25.81.456:17100?database=us_nnnn'),
('jdbc:inetpool:inetdae:10.25.81.789:17100?database=us_nnnn'),
('jdbc:inetpool:inetdae:10.25.81.012:17100?database=us_nnnn'),
('jdbc:inetpool:inetdae:10.25.81.321:17100?database=us_nnnn')
) AS data(127.0.0.1)
)
SELECT
LEFT(Item,3) AS IP4
FROM
sampledata s
CROSS APPLY
dbo.DelimitedSplit8K(s.IP,'.') dsk
WHERE
ItemNumber = 4
;WITH sampledata
AS
(
SELECT * FROM
(VALUES
('jdbc.inetpool.inetdae:10.25.81.123:17100?database=us_nnnn'),
('jdbc.inetpool.inetdae:10.25.81.456:17100?database=us_nnnn'),
('jdbc.inetpool.inetdae:10.25.81.789:17100?database=us_nnnn'),
('jdbc.inetpool.inetdae:10.25.81.012:17100?database=us_nnnn'),
('jdbc.inetpool.inetdae:10.25.81.321:17100?database=us_nnnn')
) AS data(127.0.0.1)
)
SELECT
LEFT(Item,3) AS IP4
FROM
sampledata s
CROSS APPLY
dbo.DelimitedSplit8K(s.IP,'.') dsk
WHERE
ItemNumber = 6
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply