|
|
|
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
|
|
|
|
|
Ten 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
|
|
|
|
|
Ten 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
|
|
|
|
|
Ten 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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, February 25, 2010 6:54 AM
Points: 1,
Visits: 0
|
|
| This is great information, thanks!
|
|
|
|
