sort records in postgres using just a part of string

Question

I have several records and one of my field is segment_number and it is as of:

PID_1
PID_2
PID_11
PID_14
PID_6

I want to sort all the records based on segment_number field but as its is a string and the number is at the end of the string I am not able to sort it in ascending order of numbers.

If I use ORDER BY segment_number,I get the order as this which is not what I want:

PID_1
PID_11
PID_14
PID_2
PID_6

What I want:

PID_1
PID_2
PID_6
PID_11
PID_14

Any leads will be appreciated.

You could try ordering by something like right(columnname, len(column name)-4) — procopypaster
– procopypaster, Commented Dec 28, 2021 at 5:46

Pooya · Accepted Answer · 2021-12-28 05:46:47Z

2

It is not common, but you can use functions like regexp_replace to extract numbers and sort them.

Demo

select
  *
from
  test
order by
  regexp_replace(data, '.*_([0-9]+)', '\1')::int;

answered Dec 28, 2021 at 5:46

Pooya

3,2032 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barbaros Özhan · Accepted Answer · 2021-12-28 06:30:37Z

0

You need numerical sorting.

One option would be using REGEXP_MATCHES() with \d+ pattern

SELECT col
  FROM t
 ORDER BY (REGEXP_MATCHES(col,'\d+'))[1]::INT

or REPLACE() in order to get rid of the prefix along with integer conversion such as

SELECT *
  FROM t
 ORDER BY REPLACE(col,'PID_','')::INT

if the dataset has the same pattern throughout the table

Demo

answered Dec 28, 2021 at 6:30

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Comments

rajorshi · Accepted Answer · 2021-12-28 10:09:29Z

0

Doing it by substring is much simpler than doing it by regex:

postgres=# create table test(segment_number varchar(10));
CREATE TABLE
postgres=# insert into test values ('PID_1'),('PID_11'),('PID_14'),('PID_2'),('PID_6');
INSERT 0 5

postgres=# select segment_number  from test order by substring(segment_number,5)::int;
 segment_number
----------------
 PID_1
 PID_2
 PID_6
 PID_11
 PID_14
(5 rows)

edited Dec 28, 2021 at 10:09

answered Dec 28, 2021 at 7:49

rajorshi

7174 silver badges10 bronze badges

Collectives™ on Stack Overflow

sort records in postgres using just a part of string

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related