CODEWITHSUNDEEP
sqlpostgresqlsql-order-bynatural-sort
codemode
codemode

Reputation: 1

sort records in postgres using just a part of string

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.

Upvotes: 0

Views: 1328

Answers (3)

rajorshi
rajorshi

Reputation: 717

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)

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 0

Pooya
Pooya

Reputation: 3173

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;

Upvotes: 2

Related Questions