PostgreSQL How to sort apha numeric columns?

Databases
1

Hello all,

I have a column that is of type text. It contains data such as:
A1
A10
A100
A300

B1
B10

C001
C010

Any ideas how to construct a SQL query so that these are ordered Correctly, first by Alpha, then by the number following?

Tim

2

Depending on how recent your PostgreSQL is, you can use a generated column to create an indexed sorter. This code is the proof of concept:

create temp table t (
  id bigserial primary key,
  val text not null,
  val_sorter text generated always as (
    left(val, 1) || right('00000000' || substring(val from 2), 8)
  ) stored
);

create index t_val_sorter_idx on t (val_sorter);

insert into t (
  val
) values (
  'a10'
), (
  'a2'
);

select * from t order by val_sorter;

The result of the SELECT:

id    val       val_sorter
2     a2        a00000001
1     a10       a00000123
2 Likes
3

Came here to say much the same as Kem.
The temp table is probably faster at retrieval, but you can probably do it all in the initial query, using the same logic.
Basically, adding zeroes to the left of the number part so that the string representation sorts numerically.

This example assumes the number part is never bigger than 3 digits, where Kem’s code allows for much bigger values.

select * from sometable order by left(thecolumn, 1) || right('00' || substring(thecolumn from 2), 3) ;

1 Like
4

To be clear, I used a temp table here just to illustrate how to add a generated column. That column can be added to the original table.

5

you could use this (not tested) :

SELECT 
    column_name
FROM 
    your_table
ORDER BY 
    regexp_replace(column_name, '[0-9]+', '', 'g'), -- Extract alphabetic part
    CAST(regexp_replace(column_name, '[^0-9]', '', 'g') AS INTEGER); -- Extract numeric part and convert to integer
1 Like
6

if possible split into 2 fields
category a,b,c
categorynr 1,10,100
or add a sort position field
or clean up the field to use a string sort “A001”,“B010”

order by category, categorynr 
order by position

modify data on the fly by query is not good. its better to have a good and clean database.
same if you create reports, they should only use data from database, without virtual created data.

a data field should not have multiple meanings.

1 Like
7

Your code worked PERFECTLY!

Thank you!
Tim