mysql order string as number

Question

I have searched for days for a way to sort a string as number in mysql.

My rows look like this:

order_column

1.1.2
1.1.100
1.1.1

Ordering ascending by this column in mysql will produce:

1.1.1
1.1.100
1.1.2

I am interested in getting the following result:

1.1.1
1.1.2
1.1.100

Is there a way to produce this result using SQL?

Other possible values of my column:

28.1999.1.1
1
1.1.154.20
100.1.1.1.1.15

Thanks, i appreciate everybody's time.

EDIT: Fast Relational method of storing tree data (for instance threaded comments on articles) Take a look at the first answer, the one by Ayman Hourieh. I am trying to get that solution working. My website has no more than 10k comments per article, so i found a work-around like this:

000001
000002
000002.000001
000002.000001.000001
000002.000002
000002.000003
000003

Basically, putting zeros before my comment counter so that string comparison doesn't fail. But this will only work for 99999 comments. I can add more zeros, lets say 10 zeros, and that will work for 999999999 comments, but i was hoping for a more elegant solution.

Answer 1

If string is not too long you can do

ORDER BY cast(replace(your_column_name,'.','') as unsigned);

Answer 2

You won't be able to do this efficiently since this will miss all indexes, but if it's a once-off, this will work. Sadly it looks horrible due to MySQL's inability (that I know of) to replace more than one character at a time.

SELECT value FROM TEST ORDER BY 
   REPLACE(
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE(
            REPLACE(
              REPLACE(
                REPLACE(
                  REPLACE(value, 
                    '1', '0'),
                  '2', '0'), 
                '3', '0'), 
              '4', '0'), 
            '5', '0'), 
          '6', '0'), 
        '7', '0'), 
      '8', '0'), 
    '9', '0'),value;

It will basically replace all digits with a 0 and order by that first. Since . orders before 0, it will correctly order by digit groups. When that's done, just order by value since any values with the same digit groups should order in the correct order.

To make this more efficient, you could store the replaced value as a column in the database so you could index it.

SQLfiddle demo here.