Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table data
(
  id    int not null,
  label varchar(50)
);

insert into data (id, label) values ( 1, 'DISK 2');
insert into data (id, label) values ( 2, 'DISK 5');
insert into data (id, label) values ( 3, 'DISK 33');
insert into data (id, label) values ( 4, 'DISK 7');
insert into data (id, label) values ( 5, 'DISK 15');
insert into data (id, label) values ( 6, 'DISK 27');
insert into data (id, label) values ( 7, 'DISK 8');
insert into data (id, label) values ( 8, 'DISK 10');
insert into data (id, label) values ( 9, 'DISK 1');
insert into data (id, label) values (10, 'DISK 3');
insert into data (id, label) values (11, 'DISK 9');
insert into data (id, label) values (12, 'DISK 4');
insert into data (id, label) values (13, 'DISK 20');
insert into data (id, label) values (14, 'DISK 6');
----
insert into data (id, label) values (15, 'DISK 2A');
insert into data (id, label) values (16, 'DISK 2B');
insert into data (id, label) values (17, 'DISK 2B 33');
insert into data (id, label) values (18, 'DISK 2B 4');
insert into data (id, label) values (19, 'FILE 62');
insert into data (id, label) values (20, 'FILE 7');
insert into data (id, label) values (21, 'DIRECTORY 1000000');
insert into data (id, label) values (22, 'DIRECTORY 57');
insert into data (id, label) values (23, 'DIRECTORY 9999');
insert into data (id, label) values (24, 'DIRECTORY 57000');
insert into data (id, label) values (25, 'DIRECTORY 057');
insert into data (id, label) values (26, 'DIRECTORY 0057');
insert into data (id, label) values (27, 'DIRECTORY 52');

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

-- Typical / standard string ordering (i.e. 'xx10' comes before 'xx2')
select * from data order by label asc;

id
label

26
DIRECTORY 0057

25
DIRECTORY 057

21
DIRECTORY 1000000

27
DIRECTORY 52

22
DIRECTORY 57

24
DIRECTORY 57000

23
DIRECTORY 9999

9
DISK 1

8
DISK 10

5
DISK 15

1
DISK 2

13
DISK 20

6
DISK 27

15
DISK 2A

16
DISK 2B

17
DISK 2B 33

18
DISK 2B 4

10
DISK 3

3
DISK 33

12
DISK 4

2
DISK 5

14
DISK 6

4
DISK 7

7
DISK 8

11
DISK 9

19
FILE 62

20
FILE 7

SELECT 27

-- A custom collation using Unicode's "numeric" option is needed:
-- https://www.postgresql.org/docs/11/collation.html#id-1.6.10.4.5.7.5
CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');

CREATE COLLATION

-- Desired ordering, thanks to ICU
select * from data order by label COLLATE "numeric" asc;

id
label

27
DIRECTORY 52

26
DIRECTORY 0057

25
DIRECTORY 057

22
DIRECTORY 57

23
DIRECTORY 9999

24
DIRECTORY 57000

21
DIRECTORY 1000000

9
DISK 1

1
DISK 2

15
DISK 2A

16
DISK 2B

18
DISK 2B 4

17
DISK 2B 33

10
DISK 3

12
DISK 4

2
DISK 5

14
DISK 6

4
DISK 7

7
DISK 8

11
DISK 9

8
DISK 10

5
DISK 15

13
DISK 20

6
DISK 27

3
DISK 33

20
FILE 7

19
FILE 62

SELECT 27

id	label
26	DIRECTORY 0057
25	DIRECTORY 057
21	DIRECTORY 1000000
27	DIRECTORY 52
22	DIRECTORY 57
24	DIRECTORY 57000
23	DIRECTORY 9999
9	DISK 1
8	DISK 10
5	DISK 15
1	DISK 2
13	DISK 20
6	DISK 27
15	DISK 2A
16	DISK 2B
17	DISK 2B 33
18	DISK 2B 4
10	DISK 3
3	DISK 33
12	DISK 4
2	DISK 5
14	DISK 6
4	DISK 7
7	DISK 8
11	DISK 9
19	FILE 62
20	FILE 7