clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335534 fiddles created (27400 in the last week).

CREATE TABLE alnum ( data text ); INSERT INTO alnum VALUES ('') ,(NULL) ,('A') ,('B') ,('0') ,('00') ,('0A') ,('0B') ,('00B') ,('0A0B') ,('0B0B') ,('0B00B') ,('0B000B') ,('0B0B') ,('0B0B0') ,('00B0B00') -- added special cases ,('AAA1BB') ,('AAA10B') ,('AAA20B') ,('AAA11B') ,('AAA21B') ,('X1C1') ,('X1C3') ,('X1C30') ,('X1C4') ,('X1C10') ,('X2C1') ,('X10C2') ,('X10C10') ,('1A') ,('10A') ,('2A'); CREATE TABLE expected_order ( ordinal serial primary key, sorteditem text ); -- Create composite type once per DB CREATE TYPE ai AS (a text, i int);
32 rows affected
 hidden batch(es)


SELECT data FROM alnum ORDER BY ARRAY(SELECT ROW(x[1], CASE x[2] WHEN '' THEN '0' ELSE x[2] END)::ai FROM regexp_matches(data, '(\D*)(\d*)', 'g') x) , data;
data
0
00
0A
0A0B
00B
0B
00B0B00
0B000B
0B00B
0B0B
0B0B
0B0B0
1A
2A
10A
A
AAA1BB
AAA10B
AAA11B
AAA20B
AAA21B
B
X1C1
X1C3
X1C4
X1C10
X1C30
X2C1
X10C2
X10C10
 hidden batch(es)


SELECT data FROM ( SELECT ctid, data, regexp_matches(data, '(\D*)(\d*)', 'g') AS x FROM alnum ) x GROUP BY ctid, data -- ctid as stand-in for a missing pk ORDER BY regexp_replace (left(data, 1), '[1-9]', '0') , array_agg(ROW(x[1], CASE x[2] WHEN '' THEN '0' ELSE x[2] END)::ai) , data -- for special case of trailing 0
data
0
00
0A
0A0B
00B
0B
00B0B00
0B000B
0B00B
0B0B
0B0B
0B0B0
1A
2A
10A
A
AAA1BB
AAA10B
AAA11B
AAA20B
AAA21B
B
X1C1
X1C3
X1C4
X1C10
X1C30
X2C1
X10C2
X10C10
 hidden batch(es)