Wildcarding sql statement?

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

Wildcarding sql statement?
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_M...





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 %26amp; 0xFFFFFF00) = (ip_mask.mask %26amp; 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.
Reply:wotta terrible solution. it is unnecessarily arcane, and requires changing the backend data. unless you were going out of your way to be clever on this site, i'd fire you. Report It

Reply: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.
Reply: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]))