Order by numbers

argrafic

Hello,

I have a problem when ordering by numbers.

My SQL statment is ORDER by titulo ASC, and titulo is a varchar string, it has a text name followed by a number, example:

Spot 1
Spot 2
Spot 3
...
Spot 13

The output of my SQL statement orders the results like this:
Spot 1
Spot 10
Spot 11
Spot 12
Spot 13
Spot 2... etc.

Why?

And how can I order it in the proper way? 1 followed by 2, 3, 4... 10, 11, 12, 13?

Thanks!!

sneakyimp

change the varchar field to a number field. OR maybe try "ORDER by CAST(titulo AS SIGNED) ASC"

that expression should cast titulo as an integer before sorting.

If this field is going to contain text fields too, I think you are out of luck. I could be wrong though.

bradgrafelman

I would recommend following sneakyimp's first suggestion - if the point of the column is to show some numeric identifier, then just use the number. You can always add "Spot " in the display - there's no reason to store it in the database as well.

argrafic

I have the "Spot" title to go along with the number because in the same table (it's of videos) there is another category "Cápsulas".

But reading to your replys i think I will add 2 columns, one with the type and the other with the number. That should work, right?

rulian

you owuld have to reg ex the search to eliminate non numerical characters, strict data type it to integer and then sort the numbers. that should work 99% of the time
a name like My 1st Spot 3 woudl list as 13 and not 3

bradgrafelman

argrafic wrote:
But reading to your replys i think I will add 2 columns, one with the type and the other with the number. That should work, right?

This would help simplify things, yes. Besides, one column shouldn't hold two separate types of data.