Humanizowane lub naturalne sortowanie liczb mieszanych ciągów słów i liczb

Dodanie tej odpowiedzi późno, ponieważ wyglądało na to, że wszyscy inni rozpakowują się do tablic lub czegoś takiego. Wydawało się przesadne.

CREATE FUNCTION rr(text,int) RETURNS text AS $$
SELECT regexp_replace(
    regexp_replace($1, '[0-9]+', repeat('0',$2) || '\&', 'g'), 
    '[0-9]*([0-9]{' || $2 || '})', 
    '\1', 
    'g'
)
$$ LANGUAGE sql;

SELECT t,rr(t,9) FROM mixed ORDER BY t;
      t       |             rr              
--------------+-----------------------------
 AAA02free    | AAA000000002free
 AAA10bob     | AAA000000010bob
 AAA2bbb03boo | AAA000000002bbb000000003boo
 AAA2bbb3baa  | AAA000000002bbb000000003baa
 AAA2fred     | AAA000000002fred
(5 rows)

(reverse-i-search)`OD': SELECT crypt('richpass','$2$08$aJ9ko0uKa^C1krIbdValZ.dUH8D0R0dj8mqte0Xw2FjImP5B86ugC');
richardh=> 
richardh=> SELECT t,rr(t,9) FROM mixed ORDER BY rr(t,9);
      t       |             rr              
--------------+-----------------------------
 AAA2bbb3baa  | AAA000000002bbb000000003baa
 AAA2bbb03boo | AAA000000002bbb000000003boo
 AAA2fred     | AAA000000002fred
 AAA02free    | AAA000000002free
 AAA10bob     | AAA000000010bob
(5 rows)

Nie twierdzę, że dwa wyrażenia regularne są najskuteczniejszym sposobem na to, ale rr() jest niezmienna (dla stałej długości), więc możesz ją indeksować. Oh-to jest 9.1

Oczywiście, z plperl można po prostu ocenić zamiennik do pad/przyciąć go za jednym zamachem. Ale z perlem zawsze masz tylko-jedną-więcej-opcji (TM) niż jakiekolwiek inne podejście: -)

Następująca funkcja dzieli łańcuch znaków na tablicę par (słowo, Liczba)o dowolnej długości. Jeśli łańcuch zaczyna się od liczby, to pierwszy wpis będzie miał NULL Słowo.

CREATE TYPE alnumpair AS (wordpart text,numpart integer);

CREATE OR REPLACE FUNCTION regexp_split_numstring_depth_pairs(instr text)
RETURNS alnumpair[] AS $$
WITH x(match) AS (SELECT regexp_matches($1, '(\D*)(\d+)(.*)'))
SELECT
  ARRAY[(CASE WHEN match[1] = '' THEN '0' ELSE match[1] END, match[2])::alnumpair] || (CASE 
  WHEN match[3] = '' THEN
    ARRAY[]::alnumpair[]
  ELSE 
    regexp_split_numstring_depth_pairs(match[3]) 
  END)
FROM x;$$ LANGUAGE 'sql' IMMUTABLE;

Umożliwienie sortowania typu composite PostgreSQL:

SELECT data FROM alnum ORDER BY regexp_split_numstring_depth_pairs(data);

I generowanie oczekiwanego wyniku, zgodnie z tym SQLFiddle. Zastosowałem Podstawienie Erwina 0 dla pustego łańcucha we wszystkich łańcuchach zaczynających się od liczby, więc liczby sortują się najpierw; jest czystsze niż za pomocą ORDER BY left(data,1), regexp_split_numstring_depth_pairs(data).

Chociaż funkcja jest prawdopodobnie bardzo powolna, może być przynajmniej użyta w indeksie wyrażenia.

Stanąłem przed tym samym problemem i chciałem zawinąć rozwiązanie w funkcję, aby móc go łatwo ponownie wykorzystać. Stworzyłem następującą funkcję, aby osiągnąć porządek sortowania w stylu ludzkim w Postgres.

CREATE OR REPLACE FUNCTION human_sort(text)
  RETURNS text[] AS
$BODY$   
  /* Split the input text into contiguous chunks where no numbers appear,
     and contiguous chunks of only numbers. For the numbers, add leading 
     zeros to 20 digits, so we can use one text array, but sort the 
     numbers as if they were big integers.

       For example, human_sort('Run 12 Miles') gives
            {'Run ', '00000000000000000012', ' Miles'}
  */
  select array_agg(
    case
      when a.match_array[1]::text is not null 
        then a.match_array[1]::text         
      else lpad(a.match_array[2]::text, 20::int, '0'::text)::text                                      
    end::text)
    from (
      select regexp_matches(
        case when $1 = '' then null else $1 end, E'(\\D+)|(\\d+)', 'g'
      ) AS match_array      
    ) AS a  
$BODY$
  LANGUAGE sql IMMUTABLE;

Testowany do pracy na Postgres 8.3.18 i 9.3.5

• Żadna rekurencja, powinna być szybsza od rozwiązań rekurencyjnych
• może być używany tylko w klauzuli order by, nie musisz mieć do czynienia z kluczem głównym lub ctid
• działa dla każdego select (nie trzeba nawet PK lub ctid)
• prostsze niż niektóre inne rozwiązania, powinny być łatwiejsze do rozszerzenia i utrzymania
• Nadaje się do stosowania w indeksie funkcjonalnym w celu poprawy wydajności
• działa na Postgres w wersji 8.3 lub wyższej
• pozwala na nieograniczoną liczbę przemienników tekstu / liczby na wejściu
• używa tylko jednego wyrażenia regularnego, powinno być szybsze niż wersje z wieloma wyrażeniami regularnymi
• Liczby dłuższe niż 20 cyfr są uporządkowane według pierwszych 20 cyfr

Oto przykład sposób użycia:

select * from (values 
  ('Books 1', 9),
  ('Book 20 Chapter 1', 8),
  ('Book 3 Suffix 1', 7),
  ('Book 3 Chapter 20', 6),
  ('Book 3 Chapter 2', 5),
  ('Book 3 Chapter 1', 4),
  ('Book 1 Chapter 20', 3),
  ('Book 1 Chapter 3', 2),
  ('Book 1 Chapter 1', 1),
  ('', 0),
  (null::text, 0)
) as a(name, sort)
order by human_sort(a.name)
-----------------------------
|name               |  sort |
-----------------------------
|                   |   0   |
|                   |   0   |
|Book 1 Chapter 1   |   1   |
|Book 1 Chapter 3   |   2   |
|Book 1 Chapter 20  |   3   |
|Book 3 Chapter 1   |   4   |
|Book 3 Chapter 2   |   5   |
|Book 3 Chapter 20  |   6   |
|Book 3 Suffix 1    |   7   |
|Book 20 Chapter 1  |   8   |
|Books 1            |   9   |
-----------------------------

create table dat(val text)
insert into dat ( VALUES ('BBB0adam'), ('AAA10fred'), ('AAA2fred'), ('AAA2bob') );

select 
  array_agg( case when z.x[1] ~ E'\\d' then lpad(z.x[1],10,'0') else z.x[1] end ) alnum_key
from (
  SELECT ctid, regexp_matches(dat.val, E'(\\D+|\\d+)','g') as x
  from dat
) z
group by z.ctid
order by alnum_key;

       alnum_key       
-----------------------
 {AAA,0000000002,bob}
 {AAA,0000000002,fred}
 {AAA,0000000010,fred}
 {BBB,0000000000,adam}

Pracowałem nad tym przez prawie godzinę i opublikowałem bez patrzenia -- widzę, że Erwin przybył w podobne miejsce. Natknąłem się na ten sam problem "nie można znaleźć typu tablicy dla typu danych tekst[]" jak @Clodoaldo. Miałem wiele problemów z uzyskaniem ćwiczenia czyszczenia, aby nie agg wszystkie wiersze, dopóki nie pomyślałem o grupowaniu przez ctid (co naprawdę wygląda na oszustwo - i nie działa na tabeli psuedo jak w przykładzie op WITH dat AS ( VALUES ('AAA2fred'), ('AAA10bob') ) ...). Byłoby ładniej, gdyby array_agg mógł zaakceptować zbiór wytwarzający subselect.

Nie jestem guru RegEx, ale mogę pracować nad tym do pewnego stopnia. Wystarczająco, aby uzyskać tę odpowiedź.

Obsłuży do 2 wartości liczbowych w treści. Nie sądzę, aby OSX poszedł dalej niż to, jeśli nawet obsługuje 2.

WITH parted AS (
  select data,
         substring(data from '([A-Za-z]+).*') part1,
         substring('a'||data from '[A-Za-z]+([0-9]+).*') part2,
         substring('a'||data from '[A-Za-z]+[0-9]+([A-Za-z]+).*') part3,
         substring('a'||data from '[A-Za-z]+[0-9]+[A-Za-z]+([0-9]+).*') part4
    from alnum
)
  select data
    from parted
order by part1,
         cast(part2 as int),
         part3,
         cast(part4 as int),
         data;

SQLFiddle