Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE units (id integer, unit_number text);

INSERT INTO units (id, unit_number) VALUES
  ( 1, 'A1')
, ( 2, 'A2')
, (10, 'A10')
, (11, 'B1')
, (12, 'B2')
, (20, 'B10')
;

CREATE TABLE

INSERT 0 6

-- all consist of a single leading letter and trailing digits
SELECT unit_number
FROM   units
ORDER  BY left(unit_number, 1), right(unit_number, -1)::int;

unit_number

A1

A2

A10

B1

B2

B10

SELECT 6

-- 0-n letter, followed by 0-n digits
-- add corner cases
INSERT INTO units (id, unit_number) VALUES
  (30, '3')
, (31, '2')
, (40, '10')  -- !
, (0,  'A')   -- !
, (80, 'Z')   -- !
, (81, 'Z2')   -- !
, (81, 'Z10')   -- !
, (91, 'XYZ')   -- !
, (92, '001')   -- !
, (93, '100')   -- !
;

INSERT 0 10

-- NULLS FIRST to sort missing parts on top
SELECT unit_number
FROM   units
ORDER  BY substring(unit_number, '^\D*')
        , substring(unit_number, '\d+$')::int NULLS FIRST;

unit_number

001

2

3

10

100

A

A1

A2

A10

B1

B2

B10

XYZ

Z

Z2

Z10

SELECT 16

-- Alt solution with COALESCE
SELECT unit_number
FROM   units
ORDER  BY substring(unit_number, '^\D*')
        , COALESCE(substring(unit_number, '\d+$')::int, -1);

unit_number

001

2

3

10

100

A

A1

A2

A10

B1

B2

B10

XYZ

Z

Z2

Z10

SELECT 16

-- Frank's query
SELECT unit_number
FROM  units
ORDER BY
    SUBSTRING(unit_number FROM '^[A-Za-z]+'), -- This sorts the alphabetical part.
    CAST(SUBSTRING(unit_number FROM '[0-9]+$') AS INTEGER); -- This sorts the numerical part.

unit_number

A1

A2

A10

A

B1

B2

B10

XYZ

Z2

Z10

Z

001

2

3

10

100

SELECT 16