How to exclude some letter in order by-CodePudding

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

CodePudding user response：

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 )$');