Implementing incremental search in mysql

Question

I want to implement an incremental search in MySQL. That search will be from left to right.

I have the following data in MySQL.

Test database: http://sqlfiddle.com/#!9/d23e0/1

Scenario:

+-------+
| code  |
+-------+
| 0412  |
| 05412 |
| 1412  |
| 20412 |
| 28412 |
| 30412 |
| 34120 |
| 34121 |
| 34122 |
| 37412 |
| 40412 |
| 412   |
| 41412 |
| 4412  |
| 47412 |
| 52412 |

I want to retrieve all the codes that have a '4' in it. Codes should be returned in the following order. First all codes that have a '4' in first position then '4' in second position and so on. The output should be:

+-------+
| code  |
+-------+
| 40412 |
| 412   |
| 41412 |
| 4412  |
| 47412 |
| 0412  |
| 1412  |
| 34120 |
| 34121 |
| 34122 |
| 05412 |
| 20412 |
| 28412 |
| 30412 |
| 37412 |
| 52412 |

Edit: I have modified requirement.

Answer 1

Mysql has SUBSTRING_INDEX which returns the substring on the given column based on the delimiter given. You can perform an order operation by using this. For both the scenarios you listed, you can use the following query,

SELECT code
FROM icd9_codes
WHERE code LIKE '%4%'
ORDER BY LENGTH(SUBSTRING_INDEX(code, '4', 1))

Answer 2

Note: This is the solution to the original problem which was changed (because nobody was getting it right...myself included).

Original requirement: Return only those codes that have '4' in the first position if there are no such codes then return codes that have '4' in second place and so on..

SQL Fiddle: http://sqlfiddle.com/#!9/d23e0/24

SELECT t1.code, t1.digitGroup, t1.thePosition
FROM
(
    SELECT code,
        CASE WHEN CHAR_LENGTH(code) >= 1 AND SUBSTRING(code, 1, 1) = '4' THEN 1
         WHEN CHAR_LENGTH(code) >= 2 AND SUBSTRING(code, 2, 1) = '4' THEN 2
         WHEN CHAR_LENGTH(code) >= 3 AND SUBSTRING(code, 2, 1) = '4' THEN 3
         WHEN CHAR_LENGTH(code) >= 4 AND SUBSTRING(code, 2, 1) = '4' THEN 4
         WHEN CHAR_LENGTH(code) >= 5 AND SUBSTRING(code, 2, 1) = '4' THEN 5
    ELSE 0
    END AS digitGroup,
    POSITION('4' IN code) AS thePosition
    FROM icd9_codes
) t1
WHERE t1.digitGroup > 0 AND t1.digitGroup =
    (
        SELECT MIN(t2.theMin) FROM
            (
                SELECT CASE WHEN POSITION('4' IN code) = 0 THEN 5
                    ELSE POSITION('4' IN code)
                    END AS theMin
                FROM icd9_codes
            ) t2
    )