How to sort var length ids (composite string + numeric)?

I have a MySQL database whose keys are of this type:

A_10

A_10A

A_10B

A_101

QAb801

QAc5

QAc25

QAd2993

I would like them to sort first by the alpha portion, then by the numeric portion, just like above. I would like this to be the default sorting of this column.

1) how can I sort as specified above, i.e. write a MySQL function?

2) how can I set this column to use the sorting routine by default?

some constraints that might be helpful: the numeric portion of my ID's never exceeds 100,000. I use this fact in some javascript code to convert 开发者_StackOverflow社区my ID's to strings concatenating the non-numeric portion with the (number + 1,000,000). (At the time I had not noticed the variations/subparts as above such as A_10A, A_10B, so I'll have to revamp that part of my code.)

The best way to achieve what you want is to store each part in its own column, and I would strongly recommend to change table structure. If it's impossible, you can try the following:

Create 3 UDFs which returns prefix, numeric part, and postfix of your string. For a better performance they should be native (Mysql, as any other RDMS, is not really good in complex string parsing). Then you can call these functions in ORDER BY clause or in trigger body which validates your column. In any case, it will work slower than if you create 3 columns.

No simple answer that I know of. I had something similar a while back but had to use jQuery to sort it. So what I did was first get the output into an javascript array. Then you may want to insert a zero padding to your numbers. Separate the Alpha from Nummerics using a regex, then reassemble the array:

var zarr = new Array();

for(var i=0; i<val.length; i++){

    var chunk = val[i].match(/(\d+|[^\d]+)/g).join(',');
    var chunks = chunk.split(",");

    for(var s=0; s<chunks.length; s++){

        if(isNaN(chunks[s]) == true)
            zarr.push(chunks[s]);
        else
            zarr.push(zeroPad(chunks[s], 5));

    }
}

function zeroPad(num,count){ 
var numZeropad = num + '';
while(numZeropad.length < count) {

numZeropad = "0" + numZeropad; 
}
return numZeropad;
}

You'll end up with an array like this:

A_00100
QAb00801
QAc00005
QAc00025
QAd02993

Then you can do a natural sort. I know you may want to do it through straight MySQL but I am not to sure if it does natural sorting.

Good luck!