Can SQL sort a significant portion of a number?

Question

I have a number column in the country of dialing. I want to filter the prefixes in the leftmost 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 an example of the result I want. Can sql do it easily and how or is there a better method. Keep in mind that there will be about 30 thousand lines.

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 can try the following (using MySQL INSTR()and LENGTH()):

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;

Go to @oneday, and then answer for the 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 use varcharto store numbers.

---

UPDATE:

, significant :

CREATE TABLE numbers (prefix int, significant int);

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

significant ( 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 |

...

, UPDATE , numbers.

Answer 2

'sql' ( 'MySQL'), SQL, prefix INTEGER 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;