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
– rkamun1, Commented Jun 4, 2015 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
– DRD, Commented Jun 4, 2015 at 20:20

Community · Accepted Answer · 2017-05-23 11:51:08Z

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, 2017 at 11:51

CommunityBot

11 silver badge

answered Jun 4, 2015 at 20:15

neuronaut

2,71919 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rkamun1 Over a year ago

Will doing this in ruby be able to put 2 before 10?

DRD Over a year ago

Thank you but I am using MS Sql

neuronaut Over a year ago

@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 Over a year ago

@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.

Collectives™ on Stack Overflow

Rails Activerecord order by string containing numbers

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related