StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You may want to try the following (using MySQL's <a href="http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_instr" rel="nofollow noreferrer"><code>INSTR()</code></a> and <a href="http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_length" rel="nofollow noreferrer"><code>LENGTH()</code></a> functions):</p> <pre><code>SELECT ( SELECT prefix FROM numbers n2 WHERE INSTR(n1.prefix, n2.prefix) = 1 ORDER BY LENGTH(n2.prefix) LIMIT 1 ) AS significant, n1.prefix FROM numbers n1; </code></pre> <p>Check out <a href="https://stackoverflow.com/questions/3100356/can-sql-sort-on-the-significant-part-of-a-number/3101873#3101873">@onedaywhen's answer</a> for an ANSI SQL version of the above query.</p> <p>Test case:</p> <pre><code>CREATE TABLE numbers (prefix int); INSERT INTO numbers VALUES (542); INSERT INTO numbers VALUES (54299); INSERT INTO numbers VALUES (374); INSERT INTO numbers VALUES (37477); INSERT INTO numbers VALUES (37493); INSERT INTO numbers VALUES (37494); INSERT INTO numbers VALUES (37498); INSERT INTO numbers VALUES (37447); INSERT INTO numbers VALUES (37455); INSERT INTO numbers VALUES (3749); INSERT INTO numbers VALUES (37410); INSERT INTO numbers VALUES (297); INSERT INTO numbers VALUES (29756); INSERT INTO numbers VALUES (29759); INSERT INTO numbers VALUES (29766); INSERT INTO numbers VALUES (29769); INSERT INTO numbers VALUES (29796); INSERT INTO numbers VALUES (29799); INSERT INTO numbers VALUES (29773); INSERT INTO numbers VALUES (29774); INSERT INTO numbers VALUES (297600); INSERT INTO numbers VALUES (297622); INSERT INTO numbers VALUES (247); INSERT INTO numbers VALUES (61); INSERT INTO numbers VALUES (61861); INSERT INTO numbers VALUES (61862); INSERT INTO numbers VALUES (61863); </code></pre> <p>Result:</p> <pre><code>+-------------+--------+ | significant | prefix | +-------------+--------+ | 542 | 542 | | 542 | 54299 | | 374 | 374 | | 374 | 37477 | | 374 | 37493 | | 374 | 37494 | | 374 | 37498 | | 374 | 37447 | | 374 | 37455 | | 374 | 3749 | | 374 | 37410 | | 297 | 297 | | 297 | 29756 | | 297 | 29759 | | 297 | 29766 | | 297 | 29769 | | 297 | 29796 | | 297 | 29799 | | 297 | 29773 | | 297 | 29774 | | 297 | 297600 | | 297 | 297622 | | 247 | 247 | | 61 | 61 | | 61 | 61861 | | 61 | 61862 | | 61 | 61863 | +-------------+--------+ 27 rows in set (0.00 sec) </code></pre> <p>It should work even if you are using a <code>varchar</code> to store the numbers.</p> <hr> <p><strong>UPDATE:</strong></p> <p>As for performance, you may want to consider caching the <code>significant</code> part in your table:</p> <pre><code>CREATE TABLE numbers (prefix int, significant int); -- Fill in the prefixes, leaving the significant field as NULL. </code></pre> <p>Then you could generate the <code>significant</code> field as follows (using MySQL):</p> <pre><code>UPDATE numbers n JOIN ( SELECT ( SELECT prefix FROM numbers n2 WHERE INSTR(n1.prefix, n2.prefix) = 1 ORDER BY LENGTH(n2.prefix) LIMIT 1 ) AS significant, n1.prefix FROM numbers n1 ) s ON (s.prefix = n.prefix) SET n.significant = s.significant; SELECT * FROM numbers; +--------+-------------+ | prefix | significant | +--------+-------------+ | 542 | 542 | | 54299 | 542 | | 374 | 374 | | 37477 | 374 | | 37493 | 374 | | 37494 | 374 | ... </code></pre> <p>You would probably want to run the <code>UPDATE</code> query whenever you add a new row in your <code>numbers</code> table. </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