wildcarding sql statement?

Question

hi

I have this kind of situation
2 tables, ip_masks and new_ip

(ip_masks)
ip
1.1.1
2.2.2
3.3.3
.........

(new_ip)
ip
1.1.1.1
2.2.2.2
4.4.4.4
........

so as you see the ip_mask table contains only the first 3 groups of an IP


what i need is to write a code that will select from new_ip ONLY those IPs that are in ip_mask table in a class C form

so, in our example, those would be 1.1.1.1 and 2.2.2.2

I am trying something like this

SELECT *
FROM new_ip
WHERE new_ip.ip REGEXP ' ^(SELECT * FROM ip_mask)'

which obviously doesn't work

please notice that what i try to do is WILDCARDING an SQL query but i guess the SQL simply doesn't understand that as a query but rather accepts as a string

please someone tell me how is it possible to do ?

also, it is acceptable to solve the problem in PHP if it would be executing quicker .. please tell me if the SQL or PHP would do the job faster ?

Thanks

Answer 1

Well...you could kind of fudge it a little using something like:

select *
from full_ip
where exists
  (select 1
   from ip_masks
   where substr(full_ip.ip, 1, 5) = ip_masks.mask)

But this won't work when the IPs are not single digit. Also IP masks are not always only 3 parts....read up here: http://en.wikipedia.org/wiki/IP_Subnet_Mask

There are two better ways to do what you're trying to do.

Method number 1:

Store the IP masks and the IP numbers as a single 32-bit integer.

So 128.6.4.8 would become 128 × 32³ + 6 × 32² + 4 × 32¹ + 8 = 4,200,584.

And an IP mask of 128.0.0.0 would become 128 × 32³ = 4,194,304

Then you could find matching records by using:

select full_ip.ip
from full_ip
  join ip_mask on
    (full_ip.ip & 0xFFFFFF00) = (ip_mask.mask & 0xFFFFFF00)

Advantage: you store everything in only one column, saving space.

Disadvantage: the addresses are not human-readable.

Method number 2: (Better, IMHO)

Store each IP address and mask as 4 different columns and do the same kind of thing.

select ip_1 + '.' + ip_2 + '.' + ip_3 + '.' + ip_4
from full_ip
  join ip_mask on
    ip_1 = mask_1 and
    ip_2 = mask_2 and
    ip_3 = mask_3

It's also easier to see the IP addresses in the database if you break them up like this.

If you don't understand any of this, please feel free to email me.

Answer 2

Depends (a little) on the SQL you're using.
Do following:

1. Shorten new_ip.ip by removing last ".number" from it, to make format equal to ip_masks.ip.
Let's assume you have a SQL function that does it called ShortenIP().

2. Do simple select
Select * from new_ip Where ShortenIP([ip]) in Select ip From ip_masks.


How ShortenIP() should look like depends on your SQL.
On SQL Server something like:

LEFT( [field], LEN([field]) - CHARINDEX('.', REVERSE([field]))

Answer 3

what's one in all those the reqOpen column? Is it char, varchar or boolean. in spite of if that's char, then the fact is fantastic, in spite of if that's boolean, "confident" is a million (or -a million in some databases, e.g. get right of entry to) and "No" is often 0. as a result: elect reqOpen From supRequest the place reqOpen <> 0

Answer 4

This will work for you, and you don't have to change your table-schema.

select *
from ip_masks a
inner join
new_ips b
on a.ip = left(b.ip, len(a.ip))
GO

If the first x characters in new_ips match the entire field in ip_masks with length x, the row is returned.