IP Address not formatted

  • Hello,

    I have a column that has IP addresses and some of the IP addresses are not complete. For example, there may be IP address like 8.10.168. OR 7.123.456.AVC, etc. So, in this case I would like to flag those ID's that has correct IP addresses or incorrect IP addresses. Thank you.

  • [font="Verdana"]Okay. So what's the problem with doing that?[/font]

  • Thank you Bruce.

    So, that means I would like help with T-Sql that will show me ID's with correct and incorrect IP Addresses.

    For Example: tblIP

    ID IPAdd

    101 8.143.46.

    102 8.243.6.AC

    103 192.168.1.1

    104 192.BC.168.1.1

    105 192.212.456.21

    So, I'd like help with something like this below:

    SELECT ID

    FROM IPAdd

    WHERE (....code goes here that shows either the correct IPs or Incorrect IPs).

    Thank you.

  • [font="Verdana"]Well, thinking about it, it's actually a little tricky. The best solution would be something with full regular expression handling to cope with it.

    However, you could use a pattern like:

    WHERE IPAdr not like '[0-9]%.[0-9]%.[0-9]%.[0-9]%' or IPAdr like '%[^0-9.]%'

    That will at least get you the IP addresses that contain characters that aren't digits or full stops and aren't in the form 'A.B.C.D'.

    It won't pick up something like: '01234567.89101112.13141516.17181920'.

    The only way I can think of is to pull apart the string: split it on the full stop (period) character, and check that the value of the four parts are between 0 and 255 (do a search for string splitting in SQL Server -- there are several good approaches that will do it.)

    Except... what about IP6 addresses? This will only work for IP4 addresses.

    So as I said, it's a little bit tricky. Where are you getting the IP addresses from initially?

    [/font]

  • [font="Verdana"]Here's some example code to give you an idea:

    declare @IPAddress table(AddressID int not null primary key, IPAddress varchar(15) not null);

    insert into @IPAddress

    select 101, '8.143.46.' union all

    select 102, '8.243.6.AC' union all

    select 103, '192.168.1.1' union all

    select 104, '192.BC.168.1.1' union all

    select 105, '192.212.456.21' union all

    select 106, '1.2.3.4.5.6'

    ;

    with

    MarkedAddress as (

    selectAddressID,

    IPAddress,

    0 as Marker0,

    charindex('.', IPAddress, 1) as Marker1,

    charindex('.', IPAddress, charindex('.', IPAddress, 1) + 1) as Marker2,

    charindex('.', IPAddress, charindex('.', IPAddress, charindex('.', IPAddress, 1) + 1) + 1) as Marker3,

    len(IPAddress) + 1 as Marker4

    from@IPAddress

    ),

    PartionedAddress as (

    selectAddressID,

    IPAddress,

    substring(IPAddress, Marker0 + 1, Marker1 - Marker0 - 1) as Part1,

    substring(IPAddress, Marker1 + 1, Marker2 - Marker1 - 1) as Part2,

    substring(IPAddress, Marker2 + 1, Marker3 - Marker2 - 1) as Part3,

    substring(IPAddress, Marker3 + 1, Marker4 - Marker3 - 1) as Part4

    fromMarkedAddress

    )

    select*,

    case

    when IPAddress like '%[^0-9.]%' then 0

    when IPAddress like '%.%.%.%.%' then 0

    when IPAddress not like '[0-9]%.[0-9]%.[0-9]%.[0-9]%' then 0

    when cast(Part1 as bigint) > 255 then 0

    when cast(Part2 as bigint) > 255 then 0

    when cast(Part3 as bigint) > 255 then 0

    when cast(Part4 as bigint) > 255 then 0

    else 1

    end as IsAddressValid

    fromPartionedAddress

    ;

    Edited because it didn't need the check for length zero parts as the parttern check will pick up those, and it needed an additional check if there's too many full stops in the pattern.

    [/font]

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

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