LPAD Ordering with decimals and varchar

Question

Currently I'm trying to take a column from my database and trying to sort it so that the numbers that are in the column get ordered sequentially, and then the letters with numbers follow after. When I use the following statement

SELECT * 
FROM term_size_ref 
WHERE 1=1 
ORDER BY LPAD(term_size, 10000, '0');

I get the following result where after the letters with numbers come in, the ordering becomes mixed again.

Is there a way that I can have all the numbers in order before displaying the letters? (example 0.025 -> 1.0 -> 400 -> A3)

Result of the query:

0.025
0.045
0.25
0.90
0.1
0.9
4
12
13
22
040
45
50
070
90
A1
B1
M8
RH
W1
W2
W3
1.0
1.1
1.6
1.8
100
110
187
2.3
2.4
250
3.0
4.8
400
630
8.0
800
9.5

Answer 1

Hmm, when the non numeric part is always a prefix and the other part some string that is a valid representation of a number (in decimal with optional sign) or an empty string -- your sample data suggests that --, you can first sort by the prefix lexicographically and then by the value of the number. To do that you can use regexp_replace() to remove the number or the prefix respectively.

SELECT *
       FROM term_size_ref
       ORDER BY regexp_replace(term_size,
                               '[\d.\-+]+$',
                               '') ASC,
                nullif(regexp_replace(term_size,
                                     '^[^\d.\-+]+',
                                     ''),
                       '')::decimal ASC
                                    NULLS FIRST;

db<>fiddle

If possible you might also consider to split the data and have the prefix in its own column.