Post reply

Select just three octets from IP number

  • June 30, 2008 at 7:57 am

    #193273

    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

  • June 30, 2008 at 8:01 am

    #836132

    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

  • June 30, 2008 at 8:29 am

    #836155

    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

  • June 30, 2008 at 8:32 am

    #836158

    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

  • February 25, 2010 at 7:04 am

    #1124942

    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