"Natural" Sort

[Kanwal Khipple]

My current query SELECT * FROM table ORDER BY name is coming with the following resultset:

AIYC 1
AIYC 10
AIYC 11
AIYC 12
AIYC 2
AIYC 3
...
AIYC 8
AIYC 9


I am looking for this:

AIYC 1
AIYC 2
AIYC 3
....
AIYC 8
AIYC 9
AIYC 10
AIYC 11
AIYC 12



I have tried the following:

ORDER BY ord(name)
ORDER BY soundex(name)
ORDER BY lpad(name,5, '0')
ORDER BY name + 0
ORDER BY 0 + name

None have helped.

Can anyone help me out?