StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I had a closer look on the MySQL doc pages, and I found a post there that fits your needs:</p> <pre><code>SELECT '10.0.0.1' AS IP0, ( SUBSTRING_INDEX( '10.0.0.1', '.', 1 ) * 16777216 + SUBSTRING_INDEX(SUBSTRING_INDEX( '10.0.0.1', '.', 2 ),'.',-1) * 65536 + SUBSTRING_INDEX(SUBSTRING_INDEX( '10.0.0.1', '.', -2 ),'.',1) * 256 + SUBSTRING_INDEX( '10.0.0.1', '.', -1 ) ), '10.0.255.1' AS IP1, ( SUBSTRING_INDEX( '10.0.255.1', '.', 1 ) * 16777216 + SUBSTRING_INDEX(SUBSTRING_INDEX( '10.0.255.1', '.', 2 ),'.',-1) * 65536 + SUBSTRING_INDEX(SUBSTRING_INDEX( '10.0.255.1', '.', -2 ),'.',1) * 256 + SUBSTRING_INDEX( '10.0.255.1', '.', -1 ) ) AS IP2Num1 </code></pre> <p>Returns a numeric value for each IP:</p> <pre><code>Ip: | 10.0.0.1 | 10.0.255.1 | -------------------------------- Num: | 167772161 | 167837441 | </code></pre> <p>In other words:</p> <pre><code>SELECT foo FROM bar.foobar WHERE ( SUBSTRING_INDEX( ipField, '.', 1 ) * 16777216 + SUBSTRING_INDEX(SUBSTRING_INDEX( ipField, '.', 2 ),'.',-1) * 65536 + SUBSTRING_INDEX(SUBSTRING_INDEX( ipField, '.', -2 ),'.',1) * 256 + SUBSTRING_INDEX( ipField, '.', -1 ) ) BETWEEN 167772161 AND 167837441; </code></pre> <hr> <p>Well, you <em>could</em> do this:</p> <pre><code>WHERE ipField LIKE '10.0.%.1' AND (CAST (REPLACE(SUBSTRING_INDEX(ipField,'.',3), CONCAT(SUBSTRING_INDEX(ipField ,'.',2),'.') ,'') AS UNSIGNED) BETWEEN 0 AND 255) </code></pre> <p>Which I tested with the following query:</p> <pre><code>SELECT IF ('10.0.12.1' LIKE '10.0.%.1' AND (CAST (REPLACE( SUBSTRING_INDEX('10.0.12.1','.',3), CONCAT(SUBSTRING_INDEX('10.0.12.1','.',2),'.') ,'') AS UNSIGNED) BETWEEN 0 AND 255), TRUE, FALSE); </code></pre> <p>And it returns <code>1</code>, but, honestly, that's a bit of a faff, isn't it? Why not simply:</p> <pre><code>WHERE ipField REGEXP '10\\.0\\.[0-9]{1,3}\\.1' </code></pre> <p>You can tweak this expression a bit, but basically, that seems to me to be the easiest way to query for those IP addresses.<br/>For more on MySQL string functions (which are nearly always going to be a pain to write), see <a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html" rel="nofollow">the docs here</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload