# PostgreSQL How to sort apha numeric columns?

**URL:** <https://forum.xojo.com/t/postgresql-how-to-sort-apha-numeric-columns/83441>\
**Category:** Databases\
**Created:** [December 3, 2024, 12:07am UTC](https://forum.xojo.com/t/postgresql-how-to-sort-apha-numeric-columns/83441 "2024-12-03T00:07:35Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tim\_Seyfarth](https://forum.xojo.com/letter_avatar_proxy/v4/letter/t/d26b3c/32.png) [@Tim\_Seyfarth](https://forum.xojo.com/u/Tim_Seyfarth)\
**Post date:** [December 3, 2024, 12:07am UTC](https://forum.xojo.com/t/postgresql-how-to-sort-apha-numeric-columns/83441/1 "2024-12-03T00:07:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Kem\_Tekinay](https://forum.xojo.com/user_avatar/forum.xojo.com/kem_tekinay/32/154_2.png) [@Kem\_Tekinay](https://forum.xojo.com/u/Kem_Tekinay)\
**Post date:** [December 3, 2024, 4:17am UTC](https://forum.xojo.com/t/postgresql-how-to-sort-apha-numeric-columns/83441/2 "2024-12-03T04:17:36Z")

</div>

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:

```auto
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:

```auto
id val val_sorter
2 a2 a00000001
1 a10 a00000123

```

---

<div class="post-metadata">

**Author:** ![Jeff\_Tullin](https://forum.xojo.com/user_avatar/forum.xojo.com/jeff_tullin/32/693_2.png) [@Jeff\_Tullin](https://forum.xojo.com/u/Jeff_Tullin)\
**Post date:** [December 3, 2024, 2:21pm UTC](https://forum.xojo.com/t/postgresql-how-to-sort-apha-numeric-columns/83441/3 "2024-12-03T14:21:13Z")

</div>

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) ;`

---

<div class="post-metadata">

**Author:** ![Kem\_Tekinay](https://forum.xojo.com/user_avatar/forum.xojo.com/kem_tekinay/32/154_2.png) [@Kem\_Tekinay](https://forum.xojo.com/u/Kem_Tekinay)\
**Post date:** [December 3, 2024, 2:55pm UTC](https://forum.xojo.com/t/postgresql-how-to-sort-apha-numeric-columns/83441/4 "2024-12-03T14:55:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Jean-Yves\_Pochez](https://forum.xojo.com/user_avatar/forum.xojo.com/jean-yves_pochez/32/21769_2.png) [@Jean-Yves\_Pochez](https://forum.xojo.com/u/Jean-Yves_Pochez)\
**Post date:** [December 3, 2024, 3:35pm UTC](https://forum.xojo.com/t/postgresql-how-to-sort-apha-numeric-columns/83441/5 "2024-12-03T15:35:39Z")

</div>

you could use this (not tested) :

```auto
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

```

---

<div class="post-metadata">

**Author:** ![MarkusR](https://forum.xojo.com/user_avatar/forum.xojo.com/markusr/32/685_2.png) [@MarkusR](https://forum.xojo.com/u/MarkusR)\
**Post date:** [December 3, 2024, 4:49pm UTC](https://forum.xojo.com/t/postgresql-how-to-sort-apha-numeric-columns/83441/6 "2024-12-03T16:49:04Z")

</div>

> [@Tim\_Seyfarth](#):
>
> first by Alpha, then by the number following?

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”

```auto
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 data **base**.  
same if you create reports, they should only use data from database, without virtual created data.

a data field should not have multiple meanings.

---

<div class="post-metadata">

**Author:** ![Tim\_Seyfarth](https://forum.xojo.com/letter_avatar_proxy/v4/letter/t/d26b3c/32.png) [@Tim\_Seyfarth](https://forum.xojo.com/u/Tim_Seyfarth)\
**Post date:** [December 3, 2024, 11:31pm UTC](https://forum.xojo.com/t/postgresql-how-to-sort-apha-numeric-columns/83441/7 "2024-12-03T23:31:08Z")

</div>

Your code worked PERFECTLY!

Thank you!  
Tim