Select just three octets from IP number

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Select just three octets from IP number

Select just three octets from IP number

Rate Topic

Display Mode

Topic Options

Author

Message

rodrigo.rollo

Posted Monday, June 30, 2008 7:57 AM

Forum 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

Jason Selburg

Posted Monday, June 30, 2008 8:01 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582, Visits: 3,552

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

Hari.Sharma

Posted Monday, June 30, 2008 8:29 AM

Ten 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

Hari.Sharma

Posted Monday, June 30, 2008 8:32 AM

Ten 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

robbaker

Posted Thursday, February 25, 2010 7:04 AM

Forum 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