Как отсортировать числовые строки вида «Х.Х.Х»?

Question

[kolya_krut]

Здравствуйте.

Есть поле code, в котором хранятся такого рода значения:

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

Как получить натуральную сортировку по этому полю? Результат нужно получить такой:

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

Comments

[ky0]

А как база у вас сортирует эти значения?

[kolya_krut]

ЧС.1.10 идёт после ЧС.1.2

Answer 1

[Slava Rozhnev]

Согласен с Anton Kuzmichev, однако если нет возможности изменить структуру данных, то можно разбить строку на массив и сортировать по каждому элементу

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

или так:

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

Comments

[kolya_krut]

Да, к сожалению, изменить структуру нет возможности.

Спасибо за ответ.

Чтобы не указывать каждый элемент, можно ещё сократить условие:

order by 
  (string_to_array(col, '.'))[1],
  string_to_array(substr(col, strpos(col, '.') + 1), '.')::int[]

[Anton Kuzmichev]

kolya_krut, конвертация в int[] - как раз то, чего не хватало в решении)) Выглядело не очень из-за хардкода перечислений каждого элемента массива.

Answer 2

[Anton Kuzmichev]

Чтобы не заниматься программированием в PostgreSQL, в котором натуральной сортировки для такого случая я не нашёл, решением может быть хранить ваши строки не as is, а отдельно буквы и массив цифр, т.е. вместо одного поля VARCHAR для всей строки использовать 2 поля: строка VARCHAR для букв и массив чисел 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 тогда получается банальным - сортировка просто по этим двум полям, а собрать нужную строку можно прямо в запросе:

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

Поиграться: https://www.db-fiddle.com/f/kGBapu86EXBuMZ6Bh6mGfe/1