Rails Activerecord order by string containing numbers

Question

I have the following query. ASSOC is the product category name.

q = params[:category]
@products = Product.where("ISBN = ?" ,q).order('ASSOC')

What I am getting is sorted like the following:

16 CHANNEL SECURITY DVRS
32 CHANNEL SECURITY DVRS
4 CHANNEL SECURITY DVRS
8 CHANNEL SECURITY DVRS

What I would like is:

4 CHANNEL SECURITY DVRS
8 CHANNEL SECURITY DVRS
16 CHANNEL SECURITY DVRS
32 CHANNEL SECURITY DVRS

haha...you're sorting a string.....1, 3, 4, 8. will ALL the strings be preceded by numbers? — rkamun1, Jun 04 '15 at 20:11
The string will not always be preceded by a number. Sometimes there will be no number in the string at all. — DRD, Jun 04 '15 at 20:20
I found an easy way to do this in this [answer](https://stackoverflow.com/a/66468714/7425631) — Raúl Cabrera, Jul 13 '21 at 09:45

score 1 · Answer 1 · edited May 23 '17 at 11:51

1

The order method is just passing an ORDER BY clause to the database, and it's up to the DB to provide the actual order. Most databases should be able to split your string into pieces and order the numbers numerically instead of alphabetically. I'm not sure what DB you're using, but here's a question/answer showing how to do it for MySQL: MySQL order by string with numbers

You can send longer ORDER BY clauses from Rails by including the SQL directly in the call to order, e.g.:

order('SUBSTR(col_name FROM 1 FOR 1), CAST(SUBSTR(col_name FROM 2) AS UNSIGNED)')

As an alternative, you could avoid using the order method and then sort the results using Ruby (but that's going to be less efficient so you should only sort that way if your DB can't do what I described above).

edited May 23 '17 at 11:51

Community

1
1

answered Jun 04 '15 at 20:15

neuronaut

2,689
18
24

1

Will doing this in ruby be able to put 2 before 10? – rkamun1 Jun 04 '15 at 20:23
Thank you but I am using MS Sql – DRD Jun 04 '15 at 20:27
@SirBertly Yes, however it'll take a bit of work. You'll need to split the string into the numeric part and the non-numeric part (maybe using regular expressions) and sort according to each. Take a look at the docs for Ruby's `sort` method that takes a block (and thus lets you compare strings any way you want). – neuronaut Jun 04 '15 at 20:29
@DRD The syntax will be a bit different, of course, but MS SQL Server should be able to accomplish the same thing. I'm no SQL Server expert, but googling for "sql server sort string with number" yields a lot of helpful looking results. – neuronaut Jun 04 '15 at 20:34

Rails Activerecord order by string containing numbers

1 Answers1