0

I have a table FT containing a column named IPaddress. The data in the column are all IP address in the form of "100.45.4.7". I am just wondering if I could select all those IP addresses ranging from 100.45.x.x to 100.150.x.x. (i dont care about the last two nodes). I have this following code that was running without error, but i am not sure if I got it right.

          select * from FT where IPaddress like "100.[45-150]%"

thx!

yingnan liu
  • 409
  • 1
  • 8
  • 18
  • [IP address storing in mysql database](http://stackoverflow.com/questions/6427786/ip-address-storing-in-mysql-database) – Lukasz Szozda Apr 03 '16 at 21:35

2 Answers2

4

Don't store IP addresses as strings. Store them as integers.

Use the MySQL function INET_ATON() to convert an IP address string to a number, and INET_NTOA() to convert back. Once this conversion has been performed, the numbers corresponding to IPs will be sequential, so you can use conditions like IPaddress BETWEEN INET_ATON('100.45.0.0') AND INET_ATON('100.150.255.255') in your query.

(Your programming language may have similar functions as well; for instance, PHP has ip2long() and long2ip(). The same principles will work there.)

  • thx!! i was storing IP addresses as INT. Then the data stored became "1215" instead of "100.45.7.8". Would u plz verify this again? thx a lot! – yingnan liu Apr 03 '16 at 21:48
  • @duskwuff actually all data stored as 1215, not in the original format of 4 nodes (e.g: "100.45.7.8") – yingnan liu Apr 03 '16 at 21:49
  • You've done something wrong. Make sure your column is typed as `INTEGER UNSIGNED`. –  Apr 03 '16 at 21:52
  • @duskwuff thx! i was using mysql workbench, so what i did is like i clicked those buttons on the workbench by changing the type from "varchar" to "int(200)". i guess thats where i messed up. – yingnan liu Apr 03 '16 at 21:56
  • When you change the column type of existing table, mysql will not do the conversion for you. You have to apply and update on the table to change all the rows. – Greg Borbonus Apr 03 '16 at 22:04
  • @GregBorbonus oic! thx for pointing it out. i could not revert my data from 1215 back to 100.45.7.8, i will just re-upload my data after i set the type as int. – yingnan liu Apr 03 '16 at 22:14
1

Duskwuff gave a very good answer, one I'd recommend to use over mine.

But if you're forced to keep the current structure, you can make your query work like this:

select * from FT where SUBSTRING_INDEX( IPaddress , '.', 1) = 100 AND SUBSTRING_INDEX(SUBSTRING_INDEX( IPaddress , '.', 2 ),'.',-1) BETWEEN 45 AND 150
Greg Borbonus
  • 1,384
  • 8
  • 16