Selecting part of IP address

[teksecrets]

Hi,
I have a table with an IP address field and I'd like to create a select statement to only call the 2nd and 3rd Oct.

For example:
IP address: 128.23.32.1
I'd like to select only 23.32., how would I structure my Select statement?

I have many many IP addresses in this table.

Thanks!

 

[cheyney]

assume tablename = tblIPs
assume columname = IP

select
SUBSTRING(IP, charindex('.', IP)+1, len(IP) - charindex('.', IP) - charindex('.', reverse(IP)))
FROM tblIPs

 

[cheyney]

sorry, add a +1 in there if you want the second period:

select
SUBSTRING(@c, charindex('.', @c)+1, len(@c) - charindex('.', @c) - charindex('.', reverse(@c))+1)
FROM tblIPs

 

[ClaireHCM]

--Create a user defined function for updating all the IP
----------------------------------------------------------
create function dbo.truncip (@IP varchar(20))
returns varchar(200)
as
begin
declare @1 int
declare @2 int
declare @3 int
declare @newIP varchar(200)

set @1 = charindex('.' ,@ip )
set @2 = charindex('.',@ip,@1+1)
set @3 = charindex('.',@ip,@2+1)

set @newip = substring(@ip,@1+1,@3-@1)

return @newip
end
-----------------------------------------------
--Here is how to add one more column for truncated IP
--and applied it to the whole table
alter table [Table name] add [New Column] varchar(200)

update ip1 set [New Column] = (select dbo.truncip([New column]))

claire

 

[teksecrets]

thanks again for your help.

 

[teksecrets]

Thanks,

I'm trying to use:

assume tablename = tblIPs
assume columname = IP

select
SUBSTRING(IP, charindex('.', IP)+1, len(IP) - charindex('.', IP) - charindex('.', reverse(IP)))
FROM tblIPs

BUT it leaves an extra . at the end of the ip address.
How can I structure the select statement to remove the last .?

For example,
1.12.123.12 = 12.123.
4.4.567.12 = 4.567.

How can I eliminate the last period at the end?

Thanks.

 

[cheyney]

that shouldn't leave an extra period at the end. Are you sure you're using my first post and not my second?

try trimming the IP column in the select statement:

select

SUBSTRING(LTrim(RTrim(IP)), charindex('.', LTrim(RTrim(IP)))+1, len(LTrim(RTrim(IP))) - charindex('.', LTrim(RTrim(IP))) - charindex('.', reverse(LTrim(RTrim(IP)))))

FROM tblIPs


cheyney

 

[ClaireHCM]

Just do little change in substring part

create function dbo.truncip (@IP varchar(20))
returns varchar(200)
as
begin
declare @1 int
declare @2 int
declare @3 int
declare @newIP varchar(200)

set @1 = charindex('.' ,@ip )
set @2 = charindex('.',@ip,@1+1)
set @3 = charindex('.',@ip,@2+1)

set @newip = substring(@ip,@1+1,@3-@1-1)

return @newip
end

 

[teksecrets]

THANK YOU. THIS WORKED...EXACTLY WHAT I NEEDED.

select

SUBSTRING(LTrim(RTrim(IP)), charindex('.', LTrim(RTrim(IP)))+1, len(LTrim(RTrim(IP))) - charindex('.', LTrim(RTrim(IP))) - charindex('.', reverse(LTrim(RTrim(IP)))))

FROM tblIPs