How to exclude some letter in order by

December 25, 2021

I have table with these columns: id, placement
my placement is type text and looks like this: A-L-100, A-L-101, A-R-100, …
When I do order by placement, It gives all A-L at first then A-R. Is there any option to get A-Ls and A-Rs after each other order by their first letter and their number?

Something like this A-L-100, A-R-100

my current sql:

SELECT * FROM inventory ORDER BY placement

>Solution :

It seems that you need to order by the numeric suffix first and then by the letters’ prefix. So here it is using regular expressions.

with inventory(placement) as (values ('A-L-100'), ('A-L-101'), ('A-R-100'))
select * from inventory 
 order by substring(placement from '(\d+)$'), 
          substring(placement from '(^[^\d]+)');

After your comment – order by the leading letter and then by the trailing number.

with inventory(placement) as 
(
 values 
 ('B-L-100'), ('B-L-101'), ('B-R-100'),
 ('A-L-100'), ('A-L-101'), ('A-R-100')
)
select * from inventory 
 order by left(placement, 1), substring(placement from '(\d+)$');