locked
Split IP address into four parts RRS feed

  • Question

  • Hi,

    I have to retrive all four parts of IP address using sql query. I have write a query which gives me 1st ,2nd  and 4th part. please review my query and give me proper query so I can retrive all four parts of IP address. I am not able to retrive third part of IP address

    Below is my test case and partially working query.

    create table iptest (IP	varchar(20))
    
    
    
    
    
    insert into iptest values ('83.110.218.74')
    
    insert into iptest values ('110.226.96.187')
    
    insert into iptest values ('86.96.226.13')
    
    insert into iptest values ('195.229.241.171')
    
    insert into iptest values ('206.73.209.94')
    
    insert into iptest values ('194.170.246.113')
    
    insert into iptest values ('86.99.54.194')
    
    insert into iptest values ('195.229.115.202')
    
    insert into iptest values ('116.50.167.3')
    
    insert into iptest values ('92.98.100.231')
    
    insert into iptest values ('86.98.66.139')
    
    
    
    first part query as below
    
    
    
    SELECT SUBSTRING(ip, 1, CHARINDEX('.', ip) - 1) from iptest
    
    
    
    
    
    Below query will return second and fourth part
    
    
    
    select SUBSTRING(ip, CHARINDEX('.', ip) + 1 ,
    
    CHARINDEX('.', ip,
    
    CHARINDEX('.', ip) + 1) -
    
    CHARINDEX('.', ip) - 1) ,
    
    SUBSTRING(ip, LEN(ip)+ 1 -
    
    CHARINDEX('.', REVERSE(ip)) + 1, LEN(ip)) from iptest
    
    
    
    

    With Regards, Khokhar Murtuja S Database Administrator
    • Edited by Murtuja Thursday, January 13, 2011 5:44 AM
    Thursday, January 13, 2011 5:36 AM

Answers

  • Hi,

     

    I have found simplest solution for my query! PARSENAME function worked !!!

    SELECT
            PARSENAME(ip, 4) as part1,
            PARSENAME(ip, 3) as part2,
            PARSENAME(ip, 2) as part3,
            PARSENAME(ip, 1) as part4,ip from iptest


    With Regards, Khokhar Murtuja S Database Administrator
    • Proposed as answer by Naomi N Thursday, January 13, 2011 10:34 PM
    • Marked as answer by Ai-hua Qiu Friday, January 21, 2011 6:17 AM
    Thursday, January 13, 2011 5:54 AM
  • One more Solution...  

    with part3(ip1,p3)
    AS
    (
    select ip,substring(ip,CHARINDEX('.', ip,CHARINDEX('.', ip) +1 )+1,LEN(ip)+ 1 -CHARINDEX('.', REVERSE(ip)) + 1) from iptest
    )
    select IP,SUBSTRING(ip, 1, CHARINDEX('.', ip) - 1) P1,
    SUBSTRING(ip, CHARINDEX('.', ip) + 1 ,CHARINDEX('.', ip,CHARINDEX('.', ip) + 1) -CHARINDEX('.', ip) - 1) P2,
    SUBSTRING(part3.p3, 1, CHARINDEX('.', part3.p3) - 1) P3,
    SUBSTRING(ip, LEN(ip)+ 1 -CHARINDEX('.', REVERSE(ip)) + 1, LEN(ip)) P4 from iptest,part3
    where iptest.ip=part3.ip1
    

     


    With Regards, Khokhar Murtuja S Database Administrator
    • Marked as answer by Murtuja Monday, January 24, 2011 4:36 AM
    Monday, January 17, 2011 4:36 AM

All replies

  • Hi,

    Here is a split function written by erland.

    Hope this is useful

     

    CREATE FUNCTION fn_split(@list nvarchar(MAX))
       RETURNS @tbl TABLE (number int NOT NULL) AS
    BEGIN
       declare @list nvarchar(max)
       set @list='3,5,8,7'
       DECLARE @pos        int,
               @nextpos    int,
               @valuelen   int

       SELECT @pos = 0, @nextpos = 1

       WHILE @nextpos > 0
       BEGIN
          SELECT @nextpos = charindex('.', @list, @pos + 1)
          SELECT @valuelen = CASE WHEN @nextpos > 0
                                  THEN @nextpos
                                  ELSE len(@list) + 1
                             END - @pos - 1
                            
                             select @nextpos
                             SELECT @valuelen
                              SELECT @pos = @nextpos
                             end
          INSERT @tbl (number)
             VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
          SELECT @pos = @nextpos
       END
       RETURN
    END


    Thanks and regards, Rishabh
    Thursday, January 13, 2011 5:42 AM
  • Hi,

     

    I have found simplest solution for my query! PARSENAME function worked !!!

    SELECT
            PARSENAME(ip, 4) as part1,
            PARSENAME(ip, 3) as part2,
            PARSENAME(ip, 2) as part3,
            PARSENAME(ip, 1) as part4,ip from iptest


    With Regards, Khokhar Murtuja S Database Administrator
    • Proposed as answer by Naomi N Thursday, January 13, 2011 10:34 PM
    • Marked as answer by Ai-hua Qiu Friday, January 21, 2011 6:17 AM
    Thursday, January 13, 2011 5:54 AM
  • Try this:--

    CREATE

     

    FUNCTION [dbo].[SplitString] ( @String VARCHAR (4000), @Delimiter CHAR (1)  )

    RETURNS

     

    @ValueTable TABLE (KeyWord VARCHAR(4000))

    BEGIN

     

     

    SET @String=@String+@Delimiter+'Terminator'

     

    DECLARE @Word VARCHAR(20)

     

    WHILE CHARINDEX(@Delimiter,@String,0) <> 0

     

    BEGIN

     

    SELECT

    @Word

    =RTRIM(LTRIM(SUBSTRING(@String,1,CHARINDEX(@Delimiter,@String,0)-1))),

    @String

    =RTRIM(LTRIM(SUBSTRING(@String,CHARINDEX(@Delimiter,@String,0)+1,LEN(@String))))

     

     

    IF LEN(@Word) > 0

     

    insert into @ValueTable (Keyword) Values (@Word)

     

    END

    RETURN

     

    END

     

    ------

    select

     

    * from [dbo].[SplitString] ('110.226.96.187','.')


    --------------------------------- Devender Bijania
    Thursday, January 13, 2011 5:58 AM
  • Hi,

    Thanks Murtaja for this simple approach

    but i would like to know whether this works on sql server 2005 or not?


    Thanks and regards, Rishabh
    Thursday, January 13, 2011 5:58 AM
  • Hi,

     

    I have found simplest solution for my query! PARSENAME function worked !!!

    SELECT
            PARSENAME(ip, 4) as part1,
            PARSENAME(ip, 3) as part2,
            PARSENAME(ip, 2) as part3,
            PARSENAME(ip, 1) as part4,ip from iptest


    With Regards, Khokhar Murtuja S Database Administrator
    Hi Murtuja,PARSENAME is a best approach to split an IP-address. Please consider PARSENAME can be used only to split a string which have 4 parts only or less than 4, and it can be used only when delimiter is a Dot (.). 

    --------------------------------- Devender Bijania
    Thursday, January 13, 2011 6:14 AM
  • One more Solution...  

    with part3(ip1,p3)
    AS
    (
    select ip,substring(ip,CHARINDEX('.', ip,CHARINDEX('.', ip) +1 )+1,LEN(ip)+ 1 -CHARINDEX('.', REVERSE(ip)) + 1) from iptest
    )
    select IP,SUBSTRING(ip, 1, CHARINDEX('.', ip) - 1) P1,
    SUBSTRING(ip, CHARINDEX('.', ip) + 1 ,CHARINDEX('.', ip,CHARINDEX('.', ip) + 1) -CHARINDEX('.', ip) - 1) P2,
    SUBSTRING(part3.p3, 1, CHARINDEX('.', part3.p3) - 1) P3,
    SUBSTRING(ip, LEN(ip)+ 1 -CHARINDEX('.', REVERSE(ip)) + 1, LEN(ip)) P4 from iptest,part3
    where iptest.ip=part3.ip1
    

     


    With Regards, Khokhar Murtuja S Database Administrator
    • Marked as answer by Murtuja Monday, January 24, 2011 4:36 AM
    Monday, January 17, 2011 4:36 AM
  • Hi Murtuja, If your purpose is solved. Please dont leave the thread as Unanswered, Mark Answer to the solutions.

     


    --------------------------------- Devender Bijania
    Monday, January 17, 2011 5:04 AM
  • Thankks bro
    Wednesday, June 15, 2016 4:21 AM
  • Hi,

     

    I have found simplest solution for my query! PARSENAME function worked !!!

    SELECT
            PARSENAME(ip, 4) as part1,
            PARSENAME(ip, 3) as part2,
            PARSENAME(ip, 2) as part3,
            PARSENAME(ip, 1) as part4,ip from iptest


    With Regards, Khokhar Murtuja S Database Administrator

    Wednesday, June 15, 2016 4:22 AM