Sorting strings with numbers and text in Rails

Question

In my database I have table with a name column containing grades, like 1. grade, 2. grade, and so on. When the numbers have reached 10 or more, the sorting doesn't work as I would like, as 10. grade comes before 2. grade in the sorted recordset. I know this is because string sorting is different from integer sorting. The question is how to sort these strings in a numeric way.

Because the grade-records are a part of a tree buildt with the ancestry plugin, I have to put the whole sorting code inside :order => "(some code that sorts the results)".

I have tried :order => "CAST(SUBSTRING_INDEX(name, '.') AS SIGNED)". But this doesn't work.

I use SQLite in my development environment and MySQL in the production environment.

Answer 1

try this:

replace the constant vale '. grade' of your column with empty string, then you get the numeric value. cast the same to int

order by cast(replace(name,'. grade','') as int)

EDIT:

as per your comment if its not 'grade' always, then try

order by cast(left(name,LOCATE('.',name,1)-1) as  UNSIGNED)

SQL fiddle demo