ORDER field number.number.num (varchar)

[nicdp]

I want to order a VARCHAR field with the next format: number.number.number.
Example: 1.1, 1.2, 1.14

How can I do that?

SELECT * FROM table WHERE 1=1 ORDER BY... how can I say he has to order the way I want?

[glowball]

Someone else might have a better idea, but if you're using MySQL this should work:

SELECT *
FROM tablename
ORDER BY ELT(1, REPLACE(number, '.', ',')), ELT(2, REPLACE(number, '.', ','))

Or maybe something like this (to see if it will convert the type for you):

SELECT *
FROM tablename
ORDER BY (number + 1)

You might be better off moving the data to a copied table and seeing what happens if you try to ALTER the column to make it the right datatype. I think that would be most efficient.

Find more clues at http://www.mysql.com/doc/F/u/Functions.html

[[deleted]]

Just use:
SELECT *
FROM table
ORDER BY field_name ASC;

There's no problem as long as the numbers don't start with a zero, because in alphabetical ordering, 0100 comes before 100