0

I have a list of locations within a room stored in a table. They are mostly named A1, A2, ... A16, B1, B2, ... B16, C1...

When querying against this list, how would I sort the results so that the letters are grouped with the numbers in order?

Obviously SELECT name FROM Shelves ORDER BY name doesn't work, as it returns A1, A10, ... A16, A2.

FROM Location ORDER BY LENGTH(name) doesn't work either, as the A's, B's, C's, etc are no longer grouped.

EDIT:

Not all location names are a single character followed by numbers, there is also Safe 1, Safe 2, Safe 3, Currency 1, Currency 2... (shelves within safes in the room).

A1 and such do not have a space, but Safe 1, Currency 1 and such do.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
anTrodaire
  • 93
  • 9

2 Answers2

1

Use a regex to separate names from numbers

SELECT *
FROM Shelves  
ORDER BY
regexp_replace(name , '[^a-zA-Z]*', '', 'g') ,
regexp_replace(name , '[^0-9]*', '', 'g')::INT
Mihai
  • 26,325
  • 7
  • 66
  • 81
0

This should work...
SELECT name FROM Shelves ORDER BY name ASC

Ben
  • 1