SQLServerCentral / SQL Server 2005 / T-SQL (SS2K5) / Select just three octets from IP number / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 04:53:23 GMT20RE: Select just three octets from IP numberhttp://www.sqlservercentral.com/Forums/Topic525938-338-1.aspxThis is great information, thanks!Thu, 25 Feb 2010 07:04:46 GMTrobbakerRE: Select just three octets from IP numberhttp://www.sqlservercentral.com/Forums/Topic525938-338-1.aspxAlternatively u can do:DECLARE @t VARCHAR(15)SET @t = '10.0.123.77'SELECT Left(@t,Len(@t)-CHARINDEX('.',Reverse(@t)))Mon, 30 Jun 2008 08:32:54 GMTHari.SharmaRE: Select just three octets from IP numberhttp://www.sqlservercentral.com/Forums/Topic525938-338-1.aspxDECLARE @t VARCHAR(15)SET @t = '10.0.123.77'SELECT PARSENAME(@t,4)+'.'+PARSENAME(@t,3)+'.'+PARSENAME(@t,2)Mon, 30 Jun 2008 08:29:07 GMTHari.SharmaRE: Select just three octets from IP numberhttp://www.sqlservercentral.com/Forums/Topic525938-338-1.aspxDECLARE @t VARCHAR(15)SET @t = '111.222.333.444'SELECT PARSENAME(@t,4),PARSENAME(@t,3),PARSENAME(@t,2)Mon, 30 Jun 2008 08:01:22 GMTJason SelburgSelect just three octets from IP numberhttp://www.sqlservercentral.com/Forums/Topic525938-338-1.aspxI 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 numberdeclare @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)) )) redeMon, 30 Jun 2008 07:57:08 GMTrodrigo.rollo