Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t(col VARCHAR(50))
INSERT INTO t VALUES
('PID_1'),('PID_2'),('PID_11'),('PID_14'),('PID_6')
5 rows affected

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

col

PID_1

PID_2

PID_6

PID_11

PID_14

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

col

PID_1

PID_2

PID_6

PID_11

PID_14