Hello, my first post :)
I'm currently working on a project that involves IP checking, here's my scenario:
A user enters an IP address of 10.174.55.65 let's say, I want to somehow be able to check and see if this IP address is in between column A (It's called ip) which consists the IP data of 10.174.0.0 and column B (It's called EndingIp) which has the IP data 10.174.255.255, so generally speaking I'm checking for the 3rd and 4th spots within the IP address and I'm trying to see if it's between the IP's in column A and B.
The thing is these are all strings, so how do I approch a stiuation like that within SQL?
Btw, I attached a picture of the table to be more clear.
Thanks in advance for the advice...... I'm trying to see if it's between the IP's in column A and B.If I understand it well, you could just check withWHERE val BETWEEN a AND bThis will only work if e.g. 10.1.17.32 is written as 010.001.017.032, i.e., if all four entries have 3 digits. In that case "alphabetic" order is the ordering you want.
It's probably more difficult to convert val, A and B to this format, so you will have to extract the four parts with some scalar function, recompose (either as string, in the 4x3 digit form, or as an integer: field1 x 256^3 + field2 x 256^2 + field3 x 256 + field4) and then have the BETWEEN condition.
Available scalar functions for this purpose may differ from system to system, so I won't go into details here.|||if you would kindly mention which database you're using, birko, i'll move this thread to the approproate forum where you may get more specific answers|||I'm sorry if I posted this in the wrong forum, anyways I'm using SQL 2000 server and I kind of fixed the problem of comparing within the range logically, here's what I have in a stored procedure which seems to work for IP range checking:
CREATE PROCEDURE ipRange
-- This procedure checks for the IP range.
@.ipAdd varchar(50) as
SELECT
case
when (@.ipAdd <= '10.174.90.90' and @.ipAdd >= '10.174.80.80')
then 'IP EXISTS'
END
Of course my next step is to get the values from the columns and do the comparring with them, so I'm off to do that :)
Just a background about the project, we have an ASP.NET web application coded in C# and there's a part where the user has to enter an IP address, we have to see whether the IP exists within the database or not and that's where the range check comes in since some customers have a bunch of IP addresses that range from let's say 10.174.0.0 up to 10.174.255.255, so we want to check within the range of the IP addresses in out database, it's a long project but hopfully it can be done soon.
Thank you guys :)
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment