Postgres/ActiveRecord sorting by letters then numbers?

Question

I'm trying to sort an ActiveRecord query alphabetically, case-insensitive, but with letters taking precedence over numbers.

So this:

[
  93124, my town,
  springfield,
  hooverville,
  10075, upper east side,
  Austin, TX
]

becomes:

[
  Austin, TX,
  hooverville,
  springfield,
  10075, upper east side,
  93124, my town
]

This is really eluding me.

Answer 1

You may use substring method of postgresql with a pattern.

Assuming you have Location model with name column. Column name having above values. Let's order them.

@locations = Location.select("name").order("SUBSTRING(name, '^[A-Za-z].*'), SUBSTRING(name, '^[0-9]+')::INTEGER")    

This will sort the column name first by character then by digits.

@locations.map(&:name)

=> ["Austin, TX", "hooverville", "springfield", "10075, upper east side", "93124, my town"]        

Hope this will be helpful.