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


Add to briefcase

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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 4:13 PM
Points: 2,665, Visits: 3,630
DECLARE @t VARCHAR(15)

SET @t = '111.222.333.444'

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


______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. 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: Wednesday, December 26, 2012 12:43 PM
Points: 1,330, Visits: 455
DECLARE @t VARCHAR(15)
SET @t = '10.0.123.77'
SELECT PARSENAME(@t,4)+'.'+PARSENAME(@t,3)+'.'+PARSENAME(@t,2)


Cheers,
Hari
Tips & Tricks for SQL BI Developers
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: Wednesday, December 26, 2012 12:43 PM
Points: 1,330, Visits: 455
Alternatively u can do:

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


Cheers,
Hari
Tips & Tricks for SQL BI Developers
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 »

Add to briefcase

Permissions Expand / Collapse