How to select the last segment of an IP address?

Question

Post reply

How to select the last segment of an IP address?

jordonpilling

SSCommitted

Points: 1690
More actions
January 13, 2009 at 2:13 am

#94954

Hello there,
Say ColumnA has a value of '192.168.0.27' how could i select the 27 in its own column?
i'm guessing some clever trick with charindex and substring? or perhaps a custom function to split the value? argh, there has to be a more efficient way of doing it.
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 1

Hey,

Try this

DECLARE @s-2 VARCHAR(100)

SET @s-2 = '192.168.0.27'

SELECT SUBSTRING(@s, LEN(@s) - CHARINDEX('.', REVERSE(@s)) + 2, LEN(@s))

Regards,
Nitin

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 2

Another possiblitity is this:

DECLARE @ip_part VARCHAR(15)

SET @ip_part = '192.168.0.27'

SELECT PARSENAME(@ip_part, 1)

[font="Verdana"]Markus Bohse[/font]

jordonpilling SSCommitted Points: 1690 More actions · Answer 3

parsename!!!!!!!!!!!!! how have i never come accross this function before?!?! Cheers for that!

Following now works perfect:

SELECT BRANCH_NO,

BRANCH_STARTRANGE,

PARSENAME(BRANCH_STARTRANGE,4) + '.' +

PARSENAME(BRANCH_STARTRANGE,3) + '.' +

PARSENAME(BRANCH_STARTRANGE,2) + '.' +

CAST(CAST(PARSENAME(BRANCH_STARTRANGE,1) AS INT) + 31 AS VARCHAR) AS BRANCH_ENDRANGE

FROM TBL_BRANCHDETAILS

Thanks again

Regards

Jordon

Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

jordonpilling (1/13/2009)
parsename!!!!!!!!!!!!! how have i never come accross this function before?!?! Cheers for that!
Following now works perfect:
SELECT BRANCH_NO,
BRANCH_STARTRANGE,
PARSENAME(BRANCH_STARTRANGE,4) + '.' +
PARSENAME(BRANCH_STARTRANGE,3) + '.' +
PARSENAME(BRANCH_STARTRANGE,2) + '.' +
CAST(CAST(PARSENAME(BRANCH_STARTRANGE,1) AS INT) + 31 AS VARCHAR) AS BRANCH_ENDRANGE
FROM TBL_BRANCHDETAILS
Thanks again
Regards
Jordon

Just keep in mind while using CAST/CONVERT function to convert in VARCHAR(n) and when n is not specified it defaults to 30...

--Ramesh

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 5

jordonpilling (1/13/2009)
parsename!!!!!!!!!!!!! how have i never come accross this function before?!?! Cheers for that!

Yes, it's one of the less known function, but when it comes to working with Ip-addresses it's very usefull.

[font="Verdana"]Markus Bohse[/font]

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 6

MarkusB (1/13/2009)
Another possiblitity is this:
DECLARE @ip_part VARCHAR(15)
SET @ip_part = '192.168.0.27'
SELECT PARSENAME(@ip_part, 1)

Thanks Mark,

I have read about this function but never thought to use for IP manipulation. 😛 I have been doing string manipulation for IPs in my all projects. 😛

Regards,
Nitin