Как сделать сортировку по нерегулярным буквенно-цифровым данным в Postgres SQL

Question

У меня есть следующие данные образца для конкретного символа столбца для таблицы образца.

(Обновление:) Данные не в обычном порядке. Число может встречаться в любом месте между символами.

symbol

COL4A1
COL4A3
COL8A2
COL2A1
COL12A1
COL12A1
COL16A1
COL19A1

Мне нужно отсортировать эти данные на уровне базы данных. Я использовал следующий запрос:

select symbol from sample order by symbol asc

Результат таков:

COL12A1
COL12A1
COL16A1
COL19A1
COL2A1
COL4A1
COL4A3
COL8A2

Но мне нужно получить заказ следующим образом:

COL2A1
COL4A1
COL4A3
COL8A2
COL12A1
COL12A1
COL16A1
COL19A1

Answer 1

PostgreSQL не предлагает сопоставление с учетом чисел, которое может выполнять "гуманизированные" сортировки, такие как "1A, 2A, 3A, ... 10A, 11A, ...". Он использует операционную систему для сортировки, и я не знаю ни одной ОС, которая подвергает такое сопоставление приложениям.

Для этого вам нужно разделить текст в соответствии с рисунком и порядком по частям рисунка, возможно, используя regexp_matches,

CREATE TABLE Table1 ("symbol" text);
INSERT INTO Table1 ("symbol") VALUES
    ('COL4A1'),('COL4A3'),('COL8A2'),('COL2A1'),
    ('COL12A1'),('COL12A1'),('COL16A1'),('COL19A1');

WITH matched(symbol, symbol_parts) AS (
  SELECT symbol, regexp_matches(symbol, '(\D*)(\d+)(\D+)(\d+)')
  FROM Table1
)
SELECT symbol 
FROM matched
ORDER BY symbol_parts[1], symbol_parts[2]::integer,
         symbol_parts[3], symbol_parts[4]::integer;

Answer 2

CREATE OR REPLACE FUNCTION pad_numbers(text)
              RETURNS text AS
            $BODY$
                SELECT regexp_replace(
                regexp_replace(
                  regexp_replace(
                    regexp_replace(
                      $1, 
                      E'(^|\\D)(\\d{1,3}($|\\D))', E'\\1000\\2', 'g'
                    ), E'(^|\\D)(\\d{4,6}($|\\D))', E'\\1000\\2', 'g'
                  ), E'(^|\\D)(\\d{7}($|\\D))', E'\\100\\2', 'g'
                ), E'(^|\\D)(\\d{8}($|\\D))', E'\\10\\2', 'g'
              );
            $BODY$
              LANGUAGE 'sql' VOLATILE;


select symbol from sample order by pad_numbers(symbol) asc