Kevin Waterson wrote:
well, the range could be 256, or 16, or 131072 or 2048 or any other number
really.
but the IP will always be a valid IPv4 address.
Given a valid IPv4 address, I wish to find the range it falls into.
eg: SELECT * FROM table WHERE start < 203.7.136.123 AND 203.7.136.123 < (start
plus range);
Kevin,
If I understand what you have been saying, your start address field and
range are already stored in your database as integer values (not dotted
quad strings). To perform your sample query above you will need to
convert the test address, 203.7.136.123 in this case, into an integer
value as well. The range comparison is then a trivial integer comparison.
SELECT * FROM table
WHERE start < ip2int(203.7.136.123)
AND ip2int(203.7.136.123) < (start + range);
The only problem is converting the dotted quad string to an integer
value. I have shown the use of a user defined function , ip2int(), above.
You seem reluctant to use a user defined function though. This function
could be replaced by a complicated SQL case expression that produces the
same value. The expression below converts a dotted quad string ip into
an equivalent integer value. Given that you would have to repeat the
expression twice, since sqlite does not support named subexpressions
(standard SQL WITH clause), I think the user defined function would be
much simpler.
select
case
when substr(ip, 2, 1) = '.' then -- one digit first quad
case
when substr(ip, 4, 1) = '.' then -- 1 digit second quad
case
when substr(ip, 6, 1) = '.' then -- 1 digit third quad
(substr(ip, 1, 1) << 24) +
(substr(ip, 3, 1) << 16) +
(substr(ip, 5, 1) << 8) +
substr(ip, 7, 3)
when substr(ip, 7, 1) = '.' then -- 2 digit third quad
(substr(ip, 1, 1) << 24) +
(substr(ip, 3, 1) << 16) +
(substr(ip, 5, 2) << 8) +
substr(ip, 8, 3)
else -- 3 digit third quad
(substr(ip, 1, 1) << 24) +
(substr(ip, 3, 1) << 16) +
(substr(ip, 5, 3) << 8) +
substr(ip, 9, 3)
end
when substr(ip, 5, 1) = '.' then -- 2 digit second quad
case
when substr(ip, 7, 1) = '.' then -- 1 digit third quad
(substr(ip, 1, 1) << 24) +
(substr(ip, 3, 2) << 16) +
(substr(ip, 6, 1) << 8) +
substr(ip, 8, 3)
when substr(ip, 8, 1) = '.' then -- 2 digit third quad
(substr(ip, 1, 1) << 24) +
(substr(ip, 3, 2) << 16) +
(substr(ip, 6, 2) << 8) +
substr(ip, 9, 3)
else -- 3 digit third quad
(substr(ip, 1, 1) << 24) +
(substr(ip, 3, 2) << 16) +
(substr(ip, 6, 3) << 8) +
substr(ip, 10, 3)
end
else -- 3 digit second quad
case
when substr(ip, 8, 1) = '.' then -- 1 digit third quad
(substr(ip, 1, 1) << 24) +
(substr(ip, 3, 3) << 16) +
(substr(ip, 7, 1) << 8) +
substr(ip, 9, 3)
when substr(ip, 9, 1) = '.' then -- 2 digit third quad
(substr(ip, 1, 1) << 24) +
(substr(ip, 3, 3) << 16) +
(substr(ip, 7, 2) << 8) +
substr(ip, 10, 3)
else -- 3 digit third quad
(substr(ip, 1, 1) << 24) +
(substr(ip, 3, 3) << 16) +
(substr(ip, 7, 3) << 8) +
substr(ip, 11, 3)
end
end
when substr(ip, 3, 1) = '.' then -- two digit first quad
case
when substr(ip, 5, 1) = '.' then -- 1 digit second quad
case
when substr(ip, 7, 1) = '.' then -- 1 digit third quad
(substr(ip, 1, 2) << 24) +
(substr(ip, 4, 1) << 16) +
(substr(ip, 6, 1) << 8) +
substr(ip, 8, 3)
when substr(ip, 8, 1) = '.' then -- 2 digit third quad
(substr(ip, 1, 2) << 24) +
(substr(ip, 4, 1) << 16) +
(substr(ip, 6, 2) << 8) +
substr(ip, 9, 3)
else -- 3 digit third quad
(substr(ip, 1, 2) << 24) +
(substr(ip, 4, 1) << 16) +
(substr(ip, 6, 3) << 8) +
substr(ip, 10, 3)
end
when substr(ip, 6, 1) = '.' then -- 2 digit second quad
case
when substr(ip, 8, 1) = '.' then -- 1 digit third quad
(substr(ip, 1, 2) << 24) +
(substr(ip, 4, 2) << 16) +
(substr(ip, 7, 1) << 8) +
substr(ip, 9, 3)
when substr(ip, 9, 1) = '.' then -- 2 digit third quad
(substr(ip, 1, 2) << 24) +
(substr(ip, 4, 2) << 16) +
(substr(ip, 7, 2) << 8) +
substr(ip, 10, 3)
else -- 3 digit third quad
(substr(ip, 1, 2) << 24) +
(substr(ip, 4, 2) << 16) +
(substr(ip, 7, 3) << 8) +
substr(ip, 11, 3)
end
else -- 3 digit second quad
case
when substr(ip, 9, 1) = '.' then -- 1 digit third quad
(substr(ip, 1, 2) << 24) +
(substr(ip, 4, 3) << 16) +
(substr(ip, 8, 1) << 8) +
substr(ip, 10, 3)
when substr(ip, 10, 1) = '.' then -- 2 digit third quad
(substr(ip, 1, 2) << 24) +
(substr(ip, 4, 3) << 16) +
(substr(ip, 8, 2) << 8) +
substr(ip, 11, 3)
else -- 3 digit third quad
(substr(ip, 1, 2) << 24) +
(substr(ip, 4, 3) << 16) +
(substr(ip, 8, 3) << 8) +
substr(ip, 12, 3)
end
end
else -- 3 digit first quad
case
when substr(ip, 6, 1) = '.' then -- 1 digit second quad
case
when substr(ip, 8, 1) = '.' then -- 1 digit third quad
(substr(ip, 1, 3) << 24) +
(substr(ip, 5, 1) << 16) +
(substr(ip, 7, 1) << 8) +
substr(ip, 9, 3)
when substr(ip, 9, 1) = '.' then -- 2 digit third quad
(substr(ip, 1, 3) << 24) +
(substr(ip, 5, 1) << 16) +
(substr(ip, 7, 2) << 8) +
substr(ip, 10, 3)
else -- 3 digit third quad
(substr(ip, 1, 3) << 24) +
(substr(ip, 5, 1) << 16) +
(substr(ip, 7, 3) << 8) +
substr(ip, 11, 3)
end
when substr(ip, 7, 1) = '.' then -- 2 digit second quad
case
when substr(ip, 9, 1) = '.' then -- 1 digit third quad
(substr(ip, 1, 3) << 24) +
(substr(ip, 5, 2) << 16) +
(substr(ip, 8, 1) << 8) +
substr(ip, 10, 3)
when substr(ip, 10, 1) = '.' then -- 2 digit third quad
(substr(ip, 1, 3) << 24) +
(substr(ip, 5, 2) << 16) +
(substr(ip, 8, 2) << 8) +
substr(ip, 11, 3)
else -- 3 digit third quad
(substr(ip, 1, 3) << 24) +
(substr(ip, 5, 2) << 16) +
(substr(ip, 8, 3) << 8) +
substr(ip, 12, 3)
end
else -- 3 digit second quad
case
when substr(ip, 10, 1) = '.' then -- 1 digit third quad
(substr(ip, 1, 3) << 24) +
(substr(ip, 5, 3) << 16) +
(substr(ip, 9, 1) << 8) +
substr(ip, 11, 3)
when substr(ip, 11, 1) = '.' then -- 2 digit third quad
(substr(ip, 1, 3) << 24) +
(substr(ip, 5, 3) << 16) +
(substr(ip, 9, 2) << 8) +
substr(ip, 12, 3)
else -- 3 digit third quad
(substr(ip, 1, 3) << 24) +
(substr(ip, 5, 3) << 16) +
(substr(ip, 9, 3) << 8) +
substr(ip, 13, 3)
end
end
end
from t;
Boy was I pissed the first time I typed that in and all I got was "SQL
error: near "when": syntax error". ;-)
FYI, this expression produces a VDBE program with 895 instructions.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
