sort records in postgres using just a part of string-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

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