2

Assume a table titled 'transactions'

Typical query: select * from transactions where ip=INET_ATON('127.0.0.1');

I want the ability to do a regex search for all ip's with a particular first octet (i.e. 127) but I can't quite figure out the syntax.

Thanks!

nickf
  • 537,072
  • 198
  • 649
  • 721
  • It might be too late or someone else’s schema, but IP address stored as strings are hard to work with. Four TINYINT columns are much easier to work with. Here is a good article with the more details: http://sqlserver2000.databases.aspfaq.com/how-should-i-store-an-ip-address-in-sql-server.html – NitroxDM Mar 13 '09 at 03:14

4 Answers4

1

You are comparing ip to the result of INET_ATON, which implies that ip is a 32-bit integer. Your operation should be simply...

SELECT * FROM transactions WHERE (longip & 0xFF000000) = (0x7F000000)

This will be far faster than applying a regex to a dotted IP in a string, and faster than the LIKE '127.%' solution.

Sparr
  • 7,489
  • 31
  • 48
  • good answer! you (well... I) tend to forget that IP addresses are just a single number sometimes... – nickf Mar 13 '09 at 03:26
1

‘transactions.ip’ is already an integer, which is why the questioner is using INT_ATON. It's no good trying to match it directly against a string like/regexp, you would have to convert it back to an IPv4 address first using the reverse function INT_NTOA:

SELECT * FROM transactions WHERE INT_NTOA(ip) LIKE '127.%';

This requires a complete table scan (defeating indexes) to convert and compare.

I want the ability to do a regex search for all ip's with a particular first octet (i.e. 127)

However, in the case where you want to compare against the front part of an IP address, you can take advantage of the fact that the first octet is stored in the most significant bits of the number, and say:

SELECT * FROM transactions WHERE ip BETWEEN INT_NTOA('127.0.0.0') AND INT_NTOA('127.255.255.255');

Which can be done using an index on ‘ip’ so could be considerably more efficient.

bobince
  • 528,062
  • 107
  • 651
  • 834
0

Like this?

select * from transactions where ip REGEXP "^[1-2][1-9][1-9]"
Hawk Kroeger
  • 2,336
  • 17
  • 21
  • This won't work because the first 3 digits of the numeric value of an address are not the same of the dotted-quad representation right? –  Mar 13 '09 at 02:55
0
SELECT * FROM transactions WHERE ip LIKE '127.%';
Ry Biesemeyer
  • 1,484
  • 11
  • 10