MySQL ORDER BY question

[kid_c]

Let's say I have a table with the following column:

type varchar(20) not null default ''

I'll put 3 rows in with values:

"88x31"
"120x150"
"468x60"

How can I order them so they will come out ordered smallest to greatest (ie. they should come out in the order above)

If I ORDER BY type, the 88x31 will be last since it has one less number....ordering by length() won't work.....

I know I can insert them in the right order and use their ID (i do have other fields in the real table). However this isn't an optimal way if I add new rows later on.

I know I can also split it into two integer fields, one for the num before the x, the other for the part after the x (width and height)...this will be my solution should no one have any other ...

Any ideas??

[cLFlaVA]

Without testing my SQL, I've come up with this for you to try:

SELECT * FROM table_name
ORDER BY CAST(SUBSTRING(type, 1, LOCATE(type,'x') - 1) AS int) ASC

In layman's terms, this takes the substring of type from the first character to the last character before x (in this case, "88"). It then casts that value to an integer, 88. Then, it orders it in an ascending fashion, so the smallest comes first.

You could then do the same thing for the value AFTER the x.

Hope that helps.

[cLFlaVA]

Ideally, however, to practice proper database techniques, you should have height and width as separate fields.