June 11, 2009 at 7:53 pm
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.
June 11, 2009 at 8:07 pm
[font="Verdana"]Okay. So what's the problem with doing that?[/font]
June 11, 2009 at 8:32 pm
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.
June 11, 2009 at 8:40 pm
[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]
June 11, 2009 at 9:02 pm
[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