Sorting "numbers" that are not INTs 1

[jmreinwald]

Confused? I have a trading card database I'm working through. Each card in the set has a number associated with it, but not all numbers are, well, digits. Some set numbers may be alpha characters as well, such as P-JJ. Because of the varying number of characters, I have the field defined as a varchar(8). I need a way to ORDER BY that won't sort something like

1
10
100
101
102

and so on.

 

[azzazzello]

MySQL automatically converts numbers to strings as necessary, and vice versa.

mysql> SELECT 1+'1';
-> 2
mysql> SELECT CONCAT(2,' test');
-> '2 test'

so how bout somthing like this, for example:

SELECT card FROM tradingTable ORDER BY (card + 0);

 

[jmreinwald]

Perfect!