MySQL Sort List

[xProteuSx]

Say I have a column in a MySQL database, that contains the following data (each piece of data is in its own row, as stored as a string):

 

16b

166

13A

13a

4

402c

A66

A66b

 

Currently the list sorts as follows:

 

A66

A66b

13A

13a

16b

166

4

402c

 

I need it to sort as follows:

 

A66

A66b

4

13a

13A

16b

166

402c

 

So that any strings that start with a letter are first, followed by numbers in numerical order, with lower case letters coming before upper case letters.  This is a huge issue on my website, which has a database of over 35k such numbers, split up into lists.  I can't get these sorted properly.  At least it would be nice to sort as follows:

 

1

4

100

344

 

Instead of

 

1

100

344

4

 

Know what I mean?  I know that this is complicated, because the variables are strings and not numbers, but is there an easy way to do this?

[cssfreakie]

i am not sure if i am right but wouldn't it be possible by combining:

http://www.php.net/manual/en/function.sort.php

and

http://www.php.net/manual/en/function.uasort.php

[xProteuSx]

Thanks cssfreakie.

 

That actually does not work.  I think that I am going to have to build a parser, because the data is a combination of numbers and letters, and is therefore treated as a string.  Somehow I have to split each string into components.  An example:

 

A13b

 

A = prefix

13 = numerical value

b = suffix

 

Then I can group entries by prefix, then by numerical value, then sort entries with the same numerical values by suffix.

 

I don't really have time to do this at the moment, but its definitely a project for the future.  Thanks for your input anyways.  The issue comes down to the entries being treated as strings, which is a necessity by definition.