Can SQL sort on the significant part of a number?

Question

I have a column of country dialcode numbers I want to filter the prefixes into the left most dial code

This is the source column:

prefix
------
542
54299
374
37477
37493
37494
37498
37447
37455
3749
37410
297
29756
29759
29766
29769
29796
29799
29773
29774
297600
297622
247
61
61861
61862
61863

This is a example of the result I want. Can sql do this easily and how or is there a better method. Bear in mind there will be about 30k rows

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

Answer 1

You may want to try the following (using MySQL's INSTR() and LENGTH() functions):

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;

Check out @onedaywhen's answer for an ANSI SQL version of the above query.

Test case:

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);

Result:

+-------------+--------+
| 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)

It should work even if you are using a varchar to store the numbers.

---

UPDATE:

As for performance, you may want to consider caching the significant part in your table:

CREATE TABLE numbers (prefix int, significant int);

-- Fill in the prefixes, leaving the significant field as NULL.

Then you could generate the significant field as follows (using MySQL):

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 |

...

You would probably want to run the UPDATE query whenever you add a new row in your numbers table.

Answer 2

This question has only a 'sql' tag (no 'MySQL' tag) so here's a proposed solution using Standard SQL, assuming prefix is an INTEGER column in a table named Numbers:

SELECT (
        SELECT MIN(N2.prefix)
          FROM Numbers AS N2
         WHERE CAST(N1.prefix AS VARCHAR) LIKE CAST(N2.prefix AS VARCHAR) + '%'
       ) AS significant, 
       N1.prefix
  FROM Numbers AS N1;