SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL


T-SQL

Author
Message
Viv123
Viv123
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 97
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86302 Visits: 41098
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. :-P

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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)

Group: General Forum Members
Points: 831 Visits: 1721
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([IP])
)
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([IP])
)
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([IP])
)
SELECT
LEFT(Item,3) AS IP4
FROM
sampledata s
CROSS APPLY
dbo.DelimitedSplit8K(s.IP,'.') dsk
WHERE
ItemNumber = 6





 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search