I've got a field in a table of type VARCHAR(8). The various values contained in this field include positive and negative numbers, and plain text.

When I run a query, sorting on this field, it doesn't appear to sort correctly. In my ORDER BY I'm type casting this field as follows:

ORDER BY cast(MyField as signed)

Now, if the following values are found in this field all throughout my table:

6, 3, down, slam, stgr, 0, -3

and the query is run, I get something like:

6
3
down
slam
stgr
down
0
-3

I have no idea why this sorts weirdly. I mean, even multiple instances of the same text values (like "down") aren't even appearing together.

The only thing that I find suspicious is that the Collation for this field (and all fields actually) is latin1_swedish_ci, but when I tried changing it to something like utf8_general_ci, it makes no difference.

To see this live, check out: http://virtuafighter.com/commands/index.php?chara=akira&ver=5b&order=mc&sort=desc

It's basically a command list for a video game. Clicking on the column headings sorts on that column. If you click on the "MC" column, you'll see exactly what I mean with the weird results. Sorting on the "Dmg" column seems fine, however. This field is of the same type, but does not include any text values.

Any clues on how I can sort a field with a mixture of positive and negative numbers, and text, would be appreciated.

when you cast strings to integers, they are cast to 0 if they aren't numeric.

So your actual results with sorting were

6
3
0
0
0
0
0
-3


Stop casting them to integers and you should be good to go

Thanks for the explanation.

If I don't cast, then, as an example, the number values will sort like this:

1
10
11
2
20
22

instead of:

1
2
10
11
20
22

Is there a way to correctly sort a VARCHAR field containing positive numbers, negative numbers and plain text?

Found a solution to work.

I ended up using a CASE statement in my SELECT query to assign numerical values to my strings. It basically goes:

SELECT * FROM MyTable
ORDER BY
CASE MyField
WHEN 'string1' THEN 100
WHEN 'string2' THEN 200
and so on...
ELSE cast(MyField as signed)
END

Cheers.