Post reply

Cursor and script to parse IP

  • February 16, 2012 at 3:47 pm

    #391578

    I have a field called ClientIP that holds the IP address of the device. We have several VLAN's and about 600 to 700 addresses troughout the 5 facilitites. I made this a nvarchar field because it holds 3 decimal points within the values (10.1.2.77)

    My task was to be able to find the device quickly, so I made a new field called NeworkID and ComputerID. NetworkID holds the value of the Network portion of the IP address (10.1.2) and ComputerID will hold the Node of that system (77)

    With all that out of the way, I wish to populate the NetworkID and ComputerID with the values automatically through a script that will parse the ClientIP field. I can accomplish this if I had only one line to perform the task on but I have over 600 records. I think this means that I need a cursor and I am not that well versed.

    Can anyone help me with this task and perhaps even a nice place to read about how cursors work and how they are built?

  • February 16, 2012 at 5:01 pm

    #1448237

    You can use PARSENAME function to accomplish that.

  • February 16, 2012 at 5:06 pm

    #1448239

    Some examples:

    DECLARE @IPTable TABLE ( IP_Address VARCHAR(15))

    INSERT INTO @IPTable (IP_Address)

    SELECT '1.2.3.4'

    UNION ALL SELECT '11.12.13.14'

    UNION ALL SELECT '21.22.23.24'

    UNION ALL SELECT '31.32.33.34'

    UNION ALL SELECT '253.254.255.0'

    SELECT Frst = PARSENAME(IP_Address,4)

    , Scnd = PARSENAME(IP_Address,3)

    , Thrd = PARSENAME(IP_Address,2)

    , Lst = PARSENAME(IP_Address,1)

    FROM @IPTable

  • February 17, 2012 at 11:05 am

    #1448670

    This was exactly what I needed. The task is now accomplished! Thank you!

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

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