Select rows and order by a text field as if it's numeric

[Strahan]

Hi.  I have a table with file data and one of the fields is "episode" for episode number.  If it was just plain numbers, I'd do it int and all would be happy.  However, sometimes in a folder I may have eps 1-13 then some specials which I number as SP01, SP02, etc so the episode field is varchar(10).

 

If I have these episodes in the db (2, 1, 9, 3, 4, 5, 6, 7, 8, 10, 11, 12, 13, SP01, SP02) when I list files, I do SELECT * FROM media ORDER BY disporder,episode,filename and I get:

 

1

10

11

12

13

2

3

4...etc

 

I have to make the episodes in the db as 01, 02, 03 to get the ordering right.  Is there a SQL trick wherein I can get it to order those number fields in the right order, putting the alpha stuff at the bottom or would I just have to bite the bullet and zero prefix all my single digit episodes?  Thanks!

Edited July 8, 2015 by Strahan

[mac_gyver]

if you use episode * 1 in your ORDER BY term, it should treat the values as numbers.

 

also, CAST(episode as UNSIGNED) should work as well.

[Strahan]

Thank you very much!