Sorting Column alphabetically with numbers

Question

I have table with column :name. I want ordering :name alphabetically.

Use Item.order(:name) and get "item 14", "item 15", "item 16", "item 3", "item 4". But I want "item 3", "item 4", "item 14", "item 15", "item 16"

How to achieve this?

Will the names _always_ have the same format (i.e. string, space, number)? — SRack, Jan 31 '18 at 15:59
Great, thanks for clarifying. In that case, +1 to @razvans :) — SRack, Jan 31 '18 at 16:33

score 6 · Answer 1 · edited Jan 31 '18 at 21:07

6

This seems to work

Item.order("LENGTH(name)", :name).pluck(:name)

You can check this out: MySQL 'Order By' - sorting alphanumeric correctly

edited Jan 31 '18 at 21:07

Ganesh

1,924
1
18
31

answered Jan 31 '18 at 13:47

razvans

3,172
7
22
30

Thanks, this helped solve a bug for our ordering. We needed this to order storage unit numbers. You can think of the units being numerically ordered, with the possibility of a letter in front as a prefix, e.g A1, A2...A10, A11 – VegaStudios Dec 07 '20 at 21:18

score 1 · Answer 2 · answered Jan 31 '18 at 13:43

1

Have you tried:

Item.order('CAST(name AS DECIMAL) ASC')

Just a guess, but could work... good luck!

answered Jan 31 '18 at 13:43

Ronan Lopes

3,320
4
25
51

This actually did what I was looking to do! (sort by alphabetical, then numerical). I was able to do: `order('CAST(brand AS DECIMAL) ASC').order('name ASC')` - so thank you very much for this! – Greg Blass Nov 10 '19 at 04:18

dluhhbiu · Accepted Answer · 2018-01-31T17:08:47.127

Thanks @razvans for the direction of the search. I found solution enter link description here

I have:

 id |   name   
----+----------
  2 | item 2
  3 | item 12 
  1 | item 3
  4 | item 17
  5 | df38
  6 | aaa
  7 | a13v
  8 | a13a
  9 | item 00
(9 rows)

And with this code:

select name from table_name order by left(name, 1), substring(name, '\d+')::int NULLS FIRST, name;

I got:

   name   
----------
 aaa
 a13a
 a13v
 df38
 item 00
 item 2
 item 3
 item 12 
 item 17
(9 rows)

This solution is on ruby on rails:

Item.order("left(name, 1), substring(name, '\\d+')::int NULLS FIRST, name").pluck(:name)

That's what I need!

Sorting Column alphabetically with numbers

3 Answers3