Sort by Name first then number in sql query from a delimited string

We have designed to register customer’s station-division records in 1 field. So the table rows looks like this.

create table test4 (f2 varchar(100))

insert into test4 (f2) values ('A08-0100')
insert into test4 (f2) values ('006-0100')
insert into test4 (f2) values ('008-0200')
insert into test4 (f2) values ('008-0200')
insert into test4 (f2) values ('007-0100')
insert into test4 (f2) values ('009-0100')
insert into test4 (f2) values ('009-F100')

Now I want to sort it by station, then division, but I want the letters to be displayed first on the first group.

What I’ve tried so far is

select * from test4 order by left(f2, 3), right(f2, 40)

First Group(station)        2nd Group (division)
A08                  -      0100

Expected output:

A08-0100
006-0100
007-0100
008-0200
008-0200
009-0100
009-F100

Answer

You can check if the station starts with a letter and sort it accordingly… something like this:

case when left(f2, 1) like '[a-z]%' then 1 else 2 end

Use this in the order by clause, like so:

select * from test4 order by case when left(f2, 1) like '[a-z]%' then 1 else 2 end, left(f2, 3), right(f2, 4);

See Fiddle here.