How to sort numeric strings like "X.X.X"?

Question

PostgreSQL

Hello.

There is a code field that stores such values:

ЧС.1.1
ЧС.1.2
ЧС.1.3.1
ЧС.1.10
ЧС.1.11.1
П.1.1
П.1.2
П.2.10

How to get natural sorting on this field? The result should be like this:

П.1.1
П.1.2
П.2.10
ЧС.1.1
ЧС.1.10
ЧС.1.11.1
ЧС.1.2
ЧС.1.3.1

Answer 1

I agree with Anton Kuzmichev , however, if there is no way to change the data structure, then you can split the string into an array and sort by each element

create table test (
  col varchar(64)
);

insert into test 
values 
  ('ЧС.1.1'), 
  ('ЧС.1.2'), 
  ('ЧС.1.3.1'), 
  ('ЧС.1.10'), 
  ('ЧС.1.11.1'), 
  ('П.1.1'), 
  ('П.1.2'), 
  ('П.10.2'), 
  ('П.2.10');

select 
  col
from 
  test 
order by 
  (string_to_array(col, '.'))[1], 
  (string_to_array(col, '.'))[2]::int, 
  (string_to_array(col, '.'))[3]::int;

PostgreSQL fiddle
or like this:

select col from (
  select 
  col, string_to_array(col, '.') arr
  from 
  test
) tbl
order by 
  arr[1], 
  arr[2]::int, 
  arr[3]::int;

Answer 2

In order not to engage in programming in PostgreSQL, in which I did not find a natural sorting for such a case, the solution may be to store your strings not as is , but separately letters and an array of numbers, i.e. instead of one field VARCHARfor the entire string, use 2 fields: a string VARCHARfor letters and an array of numbers INTEGER[]:

CREATE TABLE test (
  "letters" VARCHAR(20),
  "digits" INTEGER[]
);

INSERT INTO test
  ("letters", "digits")
VALUES
  ('ЧС', '{1, 1}'),
  ('ЧС', '{1, 2}'),
  ('ЧС', '{1, 3, 1}'),
  ('ЧС', '{1, 10}'),
  ('ЧС', '{1, 11, 1}'),
  ('П', '{1, 1}'),
  ('П', '{1, 2}'),
  ('П', '{2, 10}');

SQL then turns out to be banal - sorting is simply by these two fields, and you can collect the desired string directly in the query:

SELECT letters || '.' || array_to_string(digits, '.') from test order by letters, digits;

Play around: https://www.db-fiddle.com/f/kGBapu86EXBuMZ6Bh6mGfe/1