Query about 'non-standard' ORDER BY

[roark]

MySQL client version: 5.0.45

 

Hi Everyone,

I have a simple database with the filenames of images in a column called "name" (varchar(100)).

 

sample names:

Polmed_3.jpg

Polmed_11.jpg

Polmed_4.jpg

Polmed_1.jpg

Polmed_10.jpg

 

I would like to select these filenames and order them by "name" and I'm getting the following result:

 

Polmed_1.jpg

Polmed_10.jpg

Polmed_11.jpg

Polmed_2.jpg

Polmed_4.jpg

 

This is correct in a programatic way however I am looking to get the following result:

 

Polmed_1.jpg

Polmed_2.jpg

Polmed_4.jpg

Polmed_10.jpg

Polmed_11.jpg

 

Does anyone know how I would achieve this?

Thanks very much!

 

[bh]

Hi,

 

i cant find "natural" sort in mysql functions, but if the prefix (Polmed_) is always fix then you should get the number from the string.

SELECT name FROM images ORDER BY CONVERT(SUBSTRING_INDEX(name, '_', -1), UNSIGNED);

[roark]

Hi bh,

 

Thanks for your response,

I think you  may have something there.

 

The prefix unfortunately does change so there are some names that are just 1.jpg, 2.jpg, 10.jpg, 11.jpg etc

So the '_' wont be present in all the names.

 

Can you think of any other way to do it? And what is a natural sort?

 

Thank you!

[ignace]

http://stackoverflow.com/questions/153633/natural-sort-in-mysql

 

Natural sorting is: 1, 2, 10, .. instead of 1, 10, 2, ..

[roark]

Great!

 

Thanks Ignance and BH,

 

When you looking for solutions knowing the terminology is a vital prerequisite.

After knowing to search for "natural sort" I did come across a few solutions i.e.

 

Searching by lengh first then the column value

SELECT number 
       FROM tablename 
       ORDER BY LENGTH(name), name

 

Hope this helps someone.