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