Select just three octets from IP number

  • 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

  • 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[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • DECLARE @t VARCHAR(15)

    SET @t = '10.0.123.77'

    SELECT PARSENAME(@t,4)+'.'+PARSENAME(@t,3)+'.'+PARSENAME(@t,2)

  • Alternatively u can do:

    DECLARE @t VARCHAR(15)

    SET @t = '10.0.123.77'

    SELECT Left(@t,Len(@t)-CHARINDEX('.',Reverse(@t)))

  • This is great information, thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply