Natürliche Sortierung

[crystal]

Hallo Leute,

(endlich ist das Forum wieder online.)

Meine Frage:
Gibt es eine Möglichkeit, die Sortierung in Access auf 'natürlich' einzustellen?
Der Explorer sortiert z. B. so:
datei 1
datei 2
...
datei 9
datei 10
datei 11
...

Access hingegen sortiert binär, also
datei 1
datei 10
datei 11
datei 2
...
datei 9

Ich habe keine Einstellung in Access 2016 gefunden, wo man das umstellen könnte. Bleibt als einzige Möglichkeit nur, führende Nullen einzufügen, also
datei 01
datei 02
...
datei 09
datei 10
datei 11

oder gibt es eine andere Lösung?

Edit 1. Okt. 2016: habe den Thread-Titel auf "Natürliche Sortierung" geändert

[MzKlMu]

Hallo,
Du kannst über eine Abfrage die Zahl in ein extra Feld extrahieren (als Zahl!) und dann nach der Zahl sortieren.

Wie genau ist denn der Dateistring aufgebaut ?
Gibt es immer ein Leerzeichen vor der Zahl ?
Was steht denn genau in den Feldern, ein Dateiname ?

[Lachtaube]

In Access kann man nur pfuschen - ihm fehlen eigene Datentypen und Arrays nach denen sortiert werden kann. Außerdem können keine Indexe auf Ausdrücken beruhen. D. h. die nachfolgende Funktion wird mit zunehmender Datenmenge und zunehmender Verschachtelung Abfragen langsam machen.

Man lege einen VBA-Verweis auf die Microsoft VBScript Regular Expressions 5.5 Bibliothek an und stelle folgende Funktion in einem allgemeinen Modul zur Verfügung.

Code [Auswählen] Erweitern

Public Function GetSortPart$(ByVal Value, Optional ByVal PartNum& = 1)

   Static RegExp As New VBScript_RegExp_55.RegExp
   Dim matchPart$

   If Nz(Value) = vbNullString Or PartNum < 1 Or PartNum > 3 Then
      GetSortPart = "0"
   Else

      With RegExp
         '// 1) Nicht-Ziffernfolge feststellen 0..n (soviel wie möglich)
         '// 2) Ziffernfolge feststellen 0..n (soviel wie möglich)
         '// 3) der Rest
         .Pattern = "(\D*)(\d+)(.*)"
         '// Treffer auslesen
         matchPart = .Execute(Value)(0).submatches(PartNum - 1)

         Select Case PartNum
         Case 1, 2
            If Len(matchPart) Then
               '// Zeichen- oder Ziffernfolge zurückgeben
               GetSortPart = matchPart
            Else
               '// reicht zum Sortieren und kann in eine Zahl umgewandelt werden
               GetSortPart = "0"
            End If
         Case 3
            '// wir wollen den Rest hinter der Ziffernfolge
            GetSortPart = matchPart
         End Select
      End With
   End If
End Function
Angewendet auf eine Tabelle data

Code [Auswählen] Erweitern

|----+---------------|
| id | filename      |
|----+---------------|
|  1 | datei 1       |
|  2 | datei 2       |
|  3 | datei 3       |
|  4 | datei 11      |
|  5 | datei 10      |
|  6 | datei 22      |
|  7 | datei 13      |
|  8 | datei 14      |
|  9 | datei15       |
| 10 | datei7        |
| 11 | datei  19     |
| 12 | datei    1116 |
| 13 | datei      5  |
| 14 | datei  3.2    |
| 15 | datei 3.0.1   |
| 16 | datei3.0.0    |
| 17 | datei3.0.2    |
| 18 | datei   3.1.1 |
| 19 | 4   datei 28  |
| 20 | 3datei5.1     |
|----+---------------|sortiert diese Abfrage dann

Code [Auswählen] Erweitern

SELECT d.id,
       d.filename
  FROM data AS d
ORDER BY Trim(GetSortPart([filename])),
          CLng(GetSortPart([filename], 2)),
          CLng(GetSortPart(GetSortPart([filename], 3), 2)),
          CLng(GetSortPart(GetSortPart(GetSortPart([filename], 3), 3), 2));die Daten zu diesem Ergebnis:

Code [Auswählen] Erweitern

|----+---------------|
| id | filename      |
|----+---------------|
| 20 | 3datei5.1     |
| 19 | 4   datei 28  |
|  1 | datei 1       |
|  2 | datei 2       |
| 16 | datei3.0.0    |
|  3 | datei 3       |
| 15 | datei 3.0.1   |
| 17 | datei3.0.2    |
| 18 | datei   3.1.1 |
| 14 | datei  3.2    |
| 13 | datei      5  |
| 10 | datei7        |
|  5 | datei 10      |
|  4 | datei 11      |
|  7 | datei 13      |
|  8 | datei 14      |
|  9 | datei15       |
| 11 | datei  19     |
|  6 | datei 22      |
| 12 | datei    1116 |
|----+---------------|
PS: in der Praxis wird man vermutlich kaum so verrückte Leerzeichen wie im Beispiel haben.

[MzKlMu]

Hallo,
ist das nicht mit Kanonen auf Spatzen geschossen ?
Man weis ja auch nicht ob das wirklich um Dateinamen geht. Es war ja möglicherweise nur ein Beispiel.
Es wäre ja vielleicht auch sinnvoll, das in der Tabelle gleich auf 2 Felder aufzuteilen.
Und wenn wie dargestellt, die Zahl hinten steht mit einem Leerzeichen davor, kann man die Zahl mit Mid(x,y) abschneiden und als Zahl sortieren.

Daher wäre erst mal abzuwarten, bis man die angefragten Zusammenhänge/Hintergründe kennt.

[Lachtaube]

Nun, die Fragestellung ist ja nach natürlicher Sortierung ausgerichtet - egal wie und wo Ziffern angeordnet sind. Und da ist Access einfach nicht mehr zeitgemäß mit seinem uralten SQL-Dialekt. Wie eine moderne Datenbank eine natürliche Sortierung implementieren kann, ist in diesem Beispiel zu sehen.

[PhilS]

Und da ist Access einfach nicht mehr zeitgemäß mit seinem uralten SQL-Dialekt. Wie eine moderne Datenbank eine natürliche Sortierung implementieren kann, ist in diesem Beispiel zu sehen.

Das SQL Fiddle zeigt eine benutzerdefinierte Funktion. Der Implementierungsansatz ist genau der gleiche wie in deinem (übrigens sehr guten) Beispiel oben.

Die einzigen, mittelbaren Unterschiede sind, dass du mit einem Server-DBMS (SQL Fiddle war für PostgreSQL) die Funktion bereits serverseitig implementieren kannst, und, unter gewissen Umständen, auch die Ergebnisse indizieren kannst.

Access kann aufgrund seiner Architektur als Desktop-Datenbank beides nicht. Das hat aber nichts mit dem SQL-Dialekt und seiner "Modernität" zu tun.

[Lachtaube]

Ob Desktop- oder Server-Datenbanksystem dürfte für die Implementierung von Funktionalitäten eher eine untergeordnete Rolle spielen. SQLite3 ist auch eine reine Desktop-Datenbank und weist eine Schnittstelle für Collating Sequences auf (klar, auch hier muss man die Sortierung selbst implementieren).

[PhilS]

Ob Desktop- oder Server-Datenbanksystem dürfte für die Implementierung von Funktionalitäten eher eine untergeordnete Rolle spielen.

Nein, keineswegs. Eine Desktop-Datenbank führt, per Definition, die DB-Engine auf dem Client (Desktop) aus. Eine serverseitige Implementierung jedweder Funktionalität ist damit ausgeschlossen.

Eine Indizierung von funktionsbasierten, virtuellen Spalten ist auch bei den DBMS, die das grundsätzlich können, nur mit sehr strikten Einschränkungen möglich. - Mit PostgreSQL kenne ich mich kaum aus, würde aber aus wager Erinnerung dort dazu das Stichwort Purity Level anführen.

In Access/VBA ist eine Überprüfung von benutzerdefinierten VBA-Funktionen auf die dafür erforderlichen Merkmale (z.B. Determinismus) aufgrund des durch externe Bibliotheken praktisch unbegrenzten Funktionsumfangs schlichtweg unmöglich.

SQLite3 ist auch eine reine Desktop-Datenbank und weist eine Schnittstelle für Collating Sequences auf (klar, auch hier muss man die Sortierung selbst implementieren).

Das ist eine schöne Möglichkeit spezifisch Sortierreihenfolgen zu implementieren. Läuft aber, soweit ich das ohne SQLLite-Kenntnisse beurteilen kann, vom Endergebnis aus das gleiche Hinaus, wie dein Access/VBA-Beispiel.

Natürlich wäre es wünschenswert, wenn der Funktionsumfang von Access/ACE/Jet größer wäre. Die konkreten Punkte, die du hier beanstandest, haben jedoch nichts damit zu tun, dass Access "nicht modern" ist, sondern beruhen auf grundsätzlichen Architekturentscheidungen in der Implementierung der DB-Engine. Andere System mit einer vergleichbaren Architektur haben (bzw. hätten, ich weiß nicht ob es sie wirklich gibt) ähnliche Einschränkungen.

[Lachtaube]

Hier wäre eine Umsetzung für eine SQLite3-Datenbank. Der Code verwendet Olaf Schmidts hervorragendes vbRichClient-Framework mit einem VBA-Verweis auf vbRichClient5.

Zunächst wird eine Klasse für die Implementierung unserer Custom Collation benötigt.

Code [Auswählen] Erweitern

'// KlassenModul: AdditionalCollations

Option Explicit

Implements ICollation

Private NC As cStringCompare, LCID_DE&

Private Sub Class_Initialize()
  '// Wrapper um die API-Funktion CompareString
  Set NC = New_c.StringCompare
  LCID_DE = NC.MakeLCID(German_Germany)
End Sub

Private Function ICollation_CallbackCollate(ByVal ZeroBasedNameIndex As Long, _
                                            S1 As String, S2 As String) As Long
   Const cmpDigitalsAsNumbers = 8

   Select Case ZeroBasedNameIndex
   Case 0
      '// Sortierung wie im "Explorer"
      ICollation_CallbackCollate = NC.CompareString(S1, S2, _
                                                    cmpIgnoreCase Or _
                                                    cmpDigitalsAsNumbers, LCID_DE)
   End Select
End Function

Private Property Get ICollation_DefinedNames() As String
   '// Name der Sortierung festlegen
   ICollation_DefinedNames = "DE_NC"
End PropertyDer Test erfolgt in einem allgemeinen Modul.

Code [Auswählen] Erweitern

Option Compare Database
Option Explicit


Public Sub TestNaturalCollation()

   Dim cnn As cConnection, va, v

   '// Beispieldaten
   va = Array(Array("street", "9, James street"), _
              Array("street", "10, James street"), _
              Array("street", "10 ter, James street"), _
              Array("street", "10 bis"), _
              Array("street", "James street"), _
              Array("street", "5, Rudolph street"), _
              Array("version", "v1.10.1"), Array("version", "v1.10.1.10"), _
              Array("version", "v1.10.1.9"), Array("version", "v1.10.1.9.7"), _
              Array("version", "v1.10.1.9.14"), Array("version", "v1.9", "v1.10"))

   '// Memory-Datenbank erstellen
   Set cnn = New_c.Connection(OpenMode:=DBCreateInMemory)
   '// unsere Sortierung hinzufügen
   cnn.AddUserDefinedCollation New AdditionalCollations
   '// Tabelle erzeugen
   cnn.Execute "CREATE TABLE t (grp text not null, txt text not null);)"
   '// für Memory-Datenbank nicht relevant
   'cnn.BeginTrans
   '// Anfügeabfrage anlegen
   With cnn.CreateCommand("INSERT INTO t (grp, txt) VALUES (?, ?);")
      '// Beispieldaten auslesen
      For Each v In va
         '// Parameter setzen
         .SetText 1, CStr(v(0)): .SetText 2, CStr(v(1))
         '// Daten in Tabelle einfügen
         .Execute
      Next
   End With
   'cnn.CommitTrans

   '// ADODB-Recordset erzeugen
   With cnn.OpenRecordset("SELECT * FROM t ORDER BY 1, 2 COLLATE DE_NC;") _
        .GetADORsFromContent
      '// sortierte Ausgabe in Direktbereich schreiben
      Debug.Print .GetString(, , vbTab, vbCrLf)
   End With
End SubUnd hier dann das Ergebnis:

Code [Auswählen] Erweitern

street   5, Rudolph street
street   9, James street
street   10 bis
street   10 ter, James street
street   10, James street
street   James street
version  v1.9
version  v1.10.1
version  v1.10.1.9
version  v1.10.1.9.7
version  v1.10.1.9.14
version  v1.10.1.10

[crystal]

Hallo!
Sorry, dass ich erst jetzt antworte, war leider krank.

In der Tat ist mein Beispiel wohl zu stark vereinfacht, es sollte nur dazu dienen, die "natürliche" Explorer-Sortierung darzustellen. Das Wort "datei" ist keineswegs konstant, die Zahlen existieren nicht immer, es gibt also auch Daten ohne Zahlen.

Die Idee, eine API-Funktion zu nutzen, gefällt mir sehr gut, denn immerhin schafft es der Explorer ja auch.

Ich tendiere dazu, ein zusätzliches Feld "Sortierung" in meine Tabelle einzufügen und die Originaldaten in passende Sortierdaten zu wandeln. Das wäre vielleicht aus Performancegründen sinnvoll.

Erstmal vielen Dank für die Beispiele.

[PhilS]

Die Idee, eine API-Funktion zu nutzen, gefällt mir sehr gut, denn immerhin schafft es der Explorer ja auch.

Die API-Funktion, die der Windows Explorer dafür verwendet ist StrCmpLogicalW. - Allerdings habe ich im Moment keine Idee, wie man diese sinnvoll im Kontext einer Access-Abfrage einsetzen könnte.

[crystal]

Hallo PhilS,

Allerdings habe ich im Moment keine Idee, wie man diese sinnvoll im Kontext einer Access-Abfrage einsetzen könnte.

So geht' mir auch. Die einzige Möglichkeit besteht wohl darin, einen Sotierwert zusätzlich in der Tabelle zu führen.

Ich fand dafür LCMapStringEx, https://msdn.microsoft.com/en-us/library/windows/desktop/dd318702(v=vs.85).aspx

Diese Funktion konvertiert einen String entsprechend der Spracheinstellung und kennt u.a. auch das Flag SORT_DIGITSASNUMBERS beim Flag LCMAP_SORTKEY.

Aber wie kann ich LCMapStringEx in VBA benutzen? Vielleicht kannst du ein Beispiel bauen, denn wie man einen Wrapper definiert, ist zu viel für mich. Es müsste eine Funktion sein, der man den Originalstring übergibt und die dann den konvertierten String zurückgibt. Für meine Zwecke könnten Flags und andere Parameter hart gesetzt sein.

[Lachtaube]

Die Desktop-Datenbank Access hat keine öffentliche Schnittstelle, um CompareString, CompareStringEx, LCMapStringEx, lstrcmp, lstrcmp direkt nutzen zu können. Was damit geht, ist, ein Datenfeld (Array) in VBA zu sortieren.

Wie groß ist denn der Datenbestand der Tabelle, und ist Access als Backend in Stein gemeißelt?

[crystal]

Hallo Lachtaube,
Ja, ich weiß, daß Access keine "offizielle" Schnittstelle dafür hat.

Aber ich denke doch, dass es prinzipiell möglich wäre, die genannte API-Funktion aufzurufen und zu nutzen, um einmalig alle Daten meines Datenfeldes in ein zusätzliches Sortierfeld in der Tabelle zu kopieren.

Ich will also nicht ein
"ORDER BY sortnatural(tabellenfeld)",
sondern ein
"ORDER BY sortfeld"
erreichen, wobei "tabellenfeld" den Originalstring und "sortfeld" einen sortierbar konvertierten String enthält.

Ein solches Vorgehen wird übrigens auch im MSDN beim Thema "Sorting" aus Performance-Gründen empfohlen, was ja auch offensichtlich und naheliegend ist.

Um dieses "sortfeld" zu erzeugen, dachte ich mir, es wäre vielleicht sinnvoll, LCMapStringEx zu benutzen, statt eine eigene Funktion zu bauen.

LCMapStringEx übrigens sortiert nicht, sondern "übersetzt" nur einen String in einen sortierbaren Wert, der dann mit binärem, byte- oder sogar bitweisem Vergleich sortiert werden kann.

Ähnliches könnte ich für die (zu stark abstrahierten) Daten aus meinem initialen Beispiel erreichen, indem ich eine Funktion baue, die die zu sortierenden Werte schlicht mit ausreichen vielen führenden Nullen ergänzt. Aber LCMapStringEx ist ja wohl auch in der Lage, eine entspr. Übersetzung für landesspezifische Zeichen (z.B. Umlaute) zu machen. Warum also das Rad neu erfinden, statt diese API-Funktion einfach zu benutzen?

Ich bin nur leider nicht in der Lage, einen VBA-Wrapper für LCMapStringEx zu bauen.

Meine Tabelle besteht momentan aus knapp 10.000 Datensätzen, Tendenz steigend...

[daolix]

Hallo
hier ein Ansatz, ist aber so nicht voll funktionsfähig, evtl noch ein wenig mit den flag spielen:

Code [Auswählen] Erweitern


Private Declare Function LCMapStringEx Lib "KERNEL32.DLL" _
    ( _
        ByVal lpLocaleName As Long, _
        ByVal dwMapFlags As Long, _
        ByVal lpSrcStr As Long, _
        ByVal cchSrc As Long, _
        ByVal lpDestStr As Long, _
        ByVal cchDest As Long, _
        ByVal lpVersionInformation As Long, _
        ByVal lpReserved As Long, _
        ByVal lParam As Long _
) As Long

Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoW" _
    ( _
        ByVal Locale As Long, _
        ByVal LCType As Long, _
        ByVal lpLCData As Long, _
        ByVal cchData As Long _
    ) As Long
   
Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long

Private Function GetLocale(lLocaleEnum&, Optional ByVal lLCID As Long) As String
    Dim lRet As Long
    Dim sret As String
    If lLCID = 0 Then lLCID = GetUserDefaultLCID()
    lRet = GetLocaleInfo(lLCID, lLocaleEnum&, StrPtr(sret), 0)
    sret = String(lRet, 0)
    lRet = GetLocaleInfo(lLCID, lLocaleEnum&, StrPtr(sret), lRet)
    If lRet Then GetLocale = Left(sret, lRet - 1)
End Function


Public Function GetSortKeyString(ByVal stxt As String, Optional ByVal LCID As Long = 1031) As String
    Dim sLocale$
    Dim sret$
    Dim lRet&
    Const lFlag& = &H400 Or &H8
   
    sLocale = GetLocale(92, LCID)
    lRet = LCMapStringEx(StrPtr(sLocale), lFlag, StrPtr(stxt), Len(stxt), StrPtr(sret$), 0, 0, 0, 0)
    If lRet Then
        sret$ = String$(lRet, 0)
        lRet = LCMapStringEx(StrPtr(sLocale), lFlag, StrPtr(stxt), Len(stxt), StrPtr(sret$), lRet, 0, 0, 0)
        GetSortKeyString = Left(sret, lRet)
    End If
End Function


die Funktion "GetSortKeyString" dann mal in deiner Abfrage aufrufen


PS: das flag müsste wohl lauten : &H400 Or &H8 Or &H800