finding the last three digits of an ip address

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

finding the last three digits of an ip address

Author

Topic

lfmn
Posting Yak Master

141 Posts

Posted - 2002-03-11 : 16:19:51

I'm trying to get the last three digits of an ip address. below is my solution, but there HAS to be a better one. The problem is that I won't know the number of digits between each period.

declare @ip varchar(20)
set @ip = '3.4.7.0'
declare @d1 tinyint
declare @d2 tinyint
declare @d3 tinyint

select @d1 = charindex('.', @ip)
select @d2 = charindex('.', @ip, @d1+1)
select @d3 = charindex('.', @ip, @d2+1)
--select @d3

select substring(@ip, @d3+1, len(@ip))

Thanks for any help!

SQL is useful if you don't know cursors :-)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-11 : 16:38:32

select right(@ip, charindex('.', reverse(@ip))-1)
It's still pretty ugly.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-11 : 16:41:34

select right(@ip,charindex('.',reverse(@ip))-1)

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-11 : 16:42:34

I like to use the PARSENAME() function for this, even though it wasn't meant for it, it's really handy for IP addresses:

SELECT ParseName('3.4.7.0', 1) --will return 0

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-11 : 16:43:20

Oops.
Mines shorter because I didn't put a space before charindex

.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Doug G
Constraint Violating Yak Guru

331 Posts

Posted - 2002-03-11 : 20:42:13

quote:
select right(@ip, charindex('.', reverse(@ip))-1)

Don't you need another reverse in here to put the result in the correct left to right order

select reverse(right(@ip, charindex('.', reverse(@ip))-1))

(I'm not where I can test this out)

======
Doug G
======

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-11 : 21:25:06

quote:

Don't you need another reverse in here to put the result in the correct left to right order

nope.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-12 : 07:38:14

the reverse is just to find the character position from the end of the string. The actual right function is performed on @ip itself so will be in the correct order.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

lfmn
Posting Yak Master

141 Posts

Posted - 2002-03-12 : 08:51:08

Thanks to everyone for your help!

SQL is useful if you don't know cursors :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources