Sorting a Varchar Type Column with Numbers and Alpha 1

[MMNZ]

I have two VARCHAR(20) columns that looks like this (in "DataTable", which contains many other columns):

Scene_ID Shot_ID
1 10
2 2.1
2 2a
19 7
19 2.1
Test RevisionA
1 11
Temp 5b
100 83a
2 2

When I perform a simple sort:

SELECT Scene_ID, Shot_ID FROM DataTable ORDER BY Scene_ID, Shot_ID;

they are sorted alphanumerically so that "100" comes before "2" because it is being evaluated based on "1" and "2" and not the numbers compared to each other based on their numeric value. This is expected, but annoying to my application.

Before I go writing a Perl script to re-sort it the result way I want it (sorted by numbers, then letters), is there a built-in way to have MySQL format the VARCHAR columns based upon their numeric values and not necessarily their alpha-numeric values unless an alpha-character is included?

This is probablly a very common issue, and I was hoping someone could suggest a MySQL fix or a different methodology to better deal with my problem.

I am looking for output like this:

Scene_ID Shot_ID
1 10
1 11
2 2
2 2.1
2 2a
19 2.1
19 7
100 83a
Temp 5b
Test RevisionA

Thank you.

 

[sophisticate]

Well.. this gets a bit complicated, but the statement below will give you the results you're looking for. Well, almost. The ##a values come before the floating values. Perhaps someone can update the statement to change that? Basically, I'm left padding the numeric values with 0's, but there are a couple pitfalls which take some work to avoid (namely the decimals and the ##a thing). Hope this helps.

SELECT Scene_ID, Shot_ID, IF(Scene_ID REGEXP "^[[:alpha:]]",Scene_ID,LPAD(FORMAT(Scene_ID,4),10,'0')) AS scene_sort, IF(Shot_ID REGEXP "^[[:alpha:]]",Shot_ID,LPAD(FORMAT(Shot_ID,4),10,'0')) AS shot_sort FROM DataTable ORDER BY scene_sort,shot_sort,Scene_ID,Shot_ID;

brendanc@icehouse.net

http://www.witheredpipedream.com