SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On



Select just three octets from IP number Expand / Collapse
Author
Message
Posted Monday, June 30, 2008 7:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 03, 2008 6:42 AM
Points: 7, Visits: 27
I need to create a function to select just 3 octets from a column (ip_number).
I paste below the select that I did, but it is very large. anyone have any idea so that I can reduce this select?



-- I want to identify only three octets, the IP number

declare @x varchar(20)
select @x = '10.0.42.162'
select @x = '10.0.200.120'

select
@x Ip,
substring(@x,1,len(substring(@x,1,charindex('.', @x ))))+
substring(substring(@x,len(substring(@x,1,len(substring(@x,1,charindex('.', @x )))))+1,len(@x)),1,
len(substring(substring(@x,len(substring(@x,1,len(substring(@x,1,charindex('.', @x )))))+1,len(@x)),1,
charindex('.', substring(@x,len(substring(@x,1,len(substring(@x,1,charindex('.', @x )))))+1,len(@x)) )))) +
substring(SUBSTRING(@x,len(substring(@x,1,len(substring(@x,1,charindex('.', @x ))))+
substring(substring(@x,len(substring(@x,1,len(substring(@x,1,charindex('.', @x )))))+1,len(@x)),1,
len(substring(substring(@x,len(substring(@x,1,len(substring(@x,1,charindex('.', @x )))))+1,len(@x)),1,
charindex('.', substring(@x,len(substring(@x,1,len(substring(@x,1,charindex('.', @x )))))+1,len(@x)) )))))+1,
LEN(@x)),1,charindex('.', SUBSTRING(@x,len(substring(@x,1,len(substring(@x,1,charindex('.', @x ))))+
substring(substring(@x,len(substring(@x,1,len(substring(@x,1,charindex('.', @x )))))+1,len(@x)),1,
len(substring(substring(@x,len(substring(@x,1,len(substring(@x,1,charindex('.', @x )))))+1,len(@x)),1,
charindex('.', substring(@x,len(substring(@x,1,len(substring(@x,1,charindex('.', @x )))))+1,len(@x)) )))))+1,
LEN(@x)) )) rede

Post #525938
Posted Monday, June 30, 2008 8:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:47 PM
Points: 1,287, Visits: 1,699
DECLARE @t VARCHAR(15)

SET @t = '111.222.333.444'

SELECT PARSENAME(@t,4),PARSENAME(@t,3),PARSENAME(@t,2)



~Why push the envelope when you can just open it?

Jason Selburg


Post #525948
Posted Monday, June 30, 2008 8:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 30, 2009 10:12 PM
Points: 1,276, Visits: 393
DECLARE @t VARCHAR(15)
SET @t = '10.0.123.77'
SELECT PARSENAME(@t,4)+'.'+PARSENAME(@t,3)+'.'+PARSENAME(@t,2)


Cheers,
Hari
Post #525977
Posted Monday, June 30, 2008 8:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 30, 2009 10:12 PM
Points: 1,276, Visits: 393
Alternatively u can do:

DECLARE @t VARCHAR(15)
SET @t = '10.0.123.77'
SELECT Left(@t,Len(@t)-CHARINDEX('.',Reverse(@t)))


Cheers,
Hari
Post #525983
Posted Thursday, February 25, 2010 7:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 25, 2010 6:54 AM
Points: 1, Visits: 0
This is great information, thanks!
Post #872696
« Prev Topic | Next Topic »


Permissions Expand / Collapse