Oracle: Wie kann ich die Implementierung einer "natürlichen" order by in einer SQL-Abfrage?

Funktionen können Sie bei Ihrer order-by-Klausel. In diesem Fall
Sie teilen die nicht-numerische und numerische Teile der
Feld-und verwenden Sie Sie als zwei der ordnenden Kriterien.

select * from t
 order by to_number(regexp_substr(a,'^[0-9]+')),
          to_number(regexp_substr(a,'[0-9]+$')),
          a;

Können Sie auch erstellen Sie einen funktionsbasierten index, dies zu unterstützen:

create index t_ix1
    on t (to_number(regexp_substr(a, '^[0-9]+')),
          to_number(regexp_substr(a, '[0-9]+$')), 
          a);

Für kurze strings, die kleine Zahl der Numerik

Wenn die Anzahl der "Numerik" und die maximale Länge begrenzt sind, gibt es ein regexp-basierte Lösung.

Die Idee ist:

• Pad alle Numerik mit 20 Nullen
• Überflüssige Nullen mit anderen regexp. Dies könnte langsam sein aufgrund regexp backtracking.

Annahmen:

• Maximale Länge der Numerik bereits im Vorhinein bekannt ist (z.B. 20)
• Alle die Numerik kann aufgefüllt werden (in anderen Worten, lpad('1 ', 3000, '1 ') wird scheitern tun nicht fit gepolsterte Numerik in varchar2(4000))

Die folgende Abfrage ist optimiert für eine "kurz-Numerik" - Fall (siehe *?) und es dauert 0,4 Sekunden. Jedoch, wenn Sie verwenden diese Vorgehensweise, die Sie benötigen, vordefinieren Polsterung Länge.

select * from (
  select dbms_random.string('X', 30) val from xmltable('1 to 1000')
)
order by regexp_replace(regexp_replace(val, '(\d+)', lpad('0', 20, '0')||'\1')
                      , '0*?(\d{21}(\D|$))', '\1');

"Clever" - Ansatz

Obwohl separate natural_sort Funktion kann praktisch sein, es ist eine wenig bekannte trick zu tun, die in reinem SQL.

Grundgedanken:

• Strip führenden Nullen aus allen Numerik so 02 bestellt wird zwischen 1 und 3: regexp_replace(val, '(^|\D)0+(\d+)', '\1\2'). Hinweis: dies kann dazu führen, "unerwartete" Sortierung von 10.02 > 10.1 (seit 02 umgewandelt 2), jedoch gibt es keine einfache Antwort, wie Dinge wie 10.02.03 sortiert werden sollen
• Konvertieren " zu "" so dass text mit Anführungszeichen funktioniert
• Konvertieren-input-string durch Komma getrennt format: '"'||regexp_replace(..., '([^0-9]+)', '","\1","')||'"'
• Konvertieren csv, um die Liste der Elemente, die über xmltable
• Vermehren numerische-ähnliche Elemente, sodass string Sortieren funktioniert
• Verwenden length(length(num))||length(num)||num statt lpad(num, 10, '0') als die letztere ist weniger kompakt und nicht unterstützt 11+ stellige zahlen.
  Hinweis:

Reaktionszeit ist so etwas wie 3-4 Sekunden für die Sortierung der Liste von 1000 zufälligen strings der Länge 30 (die Generierung der zufälligen Zeichenfolgen dauert 0.2 sec selbst).
Der wichtigste Zeitfaktor ist xmltable dass teilt text in Zeilen.
Wenn Sie mit PL/SQL statt xmltable aufteilen, string in Zeilen, die Reaktionszeit verringert sich auf 0,4 Sek für 1000 Zeilen.

Die folgende Abfrage führt die Natürliche Sortierung von 100 zufälligen alpha-numerischen strings (Hinweis: es produziert falsche Ergebnisse in Oracle 11.2.0.4 und es funktioniert in 12.1.0.2):

select *
  from (
    select (select listagg(case when regexp_like(w, '^[0-9]')
                                then length(length(w))||length(w)||w else w
                           end
                   ) within group (order by ord)
              from xmltable(t.csv columns w varchar2(4000) path '.'
                                        , ord for ordinality) q
           ) order_by
         , t.*
    from (
           select '"'||regexp_replace(replace(
                                          regexp_replace(val, '(^|\D)0+(\d+)', '\1\2')
                                        , '"', '""')
                                    , '([^0-9]+)', '","\1","')||'"' csv
                , t.*
           from (
                  select dbms_random.string('X', 30) val from xmltable('1 to 100')
                ) t
         ) t
  ) t
order by order_by;

Den Spaß-Teil ist das order by ausgedrückt werden kann, ohne Unterabfragen, so ist es ein handliches Werkzeug, um Ihren Rezensent verrückt (es funktioniert in beide 11.2.0.4 und 12.1.0.2):

select *
  from (select dbms_random.string('X', 30) val from xmltable('1 to 100')) t
 order by (
   select listagg(case when regexp_like(w, '^[0-9]')
                       then length(length(w))||length(w)||w else w
                  end
          ) within group (order by ord)
     from xmltable('$X'
            passing xmlquery(('"'||regexp_replace(replace(
                                                     regexp_replace(t.val, '(^|\D)0+(\d+)', '\1\2')
                                                   , '"', '""')
                                                , '([^0-9]+)', '","\1","')||'"')
                             returning sequence
                    ) as X
            columns w varchar2(4000) path '.', ord for ordinality) q
);