How make for order by name in MYSQL as with order name in Window

Question

table Picture with rows have name

1.jpg,2.jpg,3.jpg,4.jpg,5.jpg,6.jpg,7.jpg,8.jpg,9.jpg,10.jpg,11.jpg
select * from Picture order by name

mysql order : 1.jpg,10.jpg,11.jpg,2.jpg,3.jpg,......

Issue:

I want it sort all type name like as with Window 1.jpg,2.jpg,3.jpg,4.jpg,5.jpg,6.jpg,7.jpg,8.jpg,9.jpg,10.jpg,11.jpg

and it must working with other case as

flower01.jpg,flower02.jpg,flower031.jpg,....,flower10.jpg

please help me

Answer 1

A hackish way to do it:

... ORDER BY CAST(name AS UNSIGNED);

---

Edit: an alternative would be:

 ... ORDER BY LPAD(name,N,'0');

Where N is the maximum width of your column. Sadly, MySQL doesn't support natural sorting natively AFAIK.

Answer 2

ODER BY name sees that name is a string and sorts it accordingly,charater by character. You have to make MySQL interpret the name as numeric value. A way might be something like this:

select * from Picture order by name * 1;

Answer 3

You basically have 4 choices.

1. Change name to a numeric type, and remove the .jpg (adding it later in code)
2. Add an 'order' column and sort by that
3. Sort it in your code, not in SQL
4. Use of the of the cast 'hacks' (e.g. CAST(NAME AS UNSIGNED)