Chr Feld numerisch sortieren

[Walter Landwehr]

Hallo,
kennt jemand eine Routine in Delphi oder SQL die ein VarChr Feld numerisch sortiert.
Beispiel:

1
10
21.2
35.1
4
2
2a
...


Das Ergebnis sollte dann so aussehen

1
2
2a
4
10
21.2
35.1
...

ich habe in der Tabelle eigens ein Dezimalfeld = 'Sortierung' für die Sortierung. Der zu sortierende Wert steht im Feld Ziffer.

[dataspider]

Da du auch Buchstaben im Wert hast, dürfte es mit SQL "schwer" sein.

Du willst ja nach dem Zahlenanteil sortieren, und den musst du dann erst mal extrahieren.

Ich würde eine SP schreiben, die dir den Dezimalwert zurück liefert.

Dann kann man mit
order by (select result from SP_STRING_AS_DECIMAL(WERT))
sortieren.

So eine SP ist ja easy in wenigen Minuten geschrieben...

SP könnte so aussehen:

Code:

SET TERM ^ ;

create or alter procedure sp_string_as_decimal (
    wert varchar(20))
returns (
    result numeric(15,2))
as
declare variable laenge smallint;
declare variable pos smallint;
declare variable zeichen char(1);
declare variable temp varchar(20);
begin
  result = null;
  laenge = char_length(:wert);
  if (:laenge > 0) then
  begin
    pos = 1;
    while (:pos <= :laenge) do
    begin
      zeichen = substring(:wert from :pos for 1);
      if (:zeichen in ('0','1','2','3','4','5','6','7','8','9', '.')) then
        temp = coalesce(:temp, '')||:zeichen;
      else
        break;
      pos = :pos + 1;
    end 
  end
  if (coalesce(:temp, '') <> '') then
    result = cast(:temp as numeric(15, 2));
  suspend;
end^

SET TERM ; ^

Frank

[Walter Landwehr]

Hallo Frank,

fast perfekt nur die Nachkomma stimmen noch nicht.

14.1
14.2
14.10

wird falsch sortiert, nämlich so:
14.1
14.10
14.2

sollte aber wie oben sortiert sein.

[TBx]

Das liegt daran, dass dezimal 1.1 und 1.10 identisch ist.
Will man den Teil vor und nach dem Punkt wie im Beispiel sortieren, so muss man zwei Felder daraus machen und diese dann sortieren.

Wie ist denn die Zusammensetzung der Werte definiert? Ist der Punkt der Trenner und die einzelnen Zahlen können durch Buchstaben ergänzt werden?

Sieht mir nach Abrechnungsziffern aus.

[dataspider]

Ja, so sieht das natürlich anders aus.
Du musst gedanklich einen sortierbaren String basteln.

Wenn also nach dem Zahlenwert vor dem Punkt und dann nach den String nach dem Punkt sortiert werden soll,
kann man den Wert vor dem Punkt links auffüllen, den Wert nach dem Punkt übernehmen.

somit kann aus 14.1 0000000014.1
und aus 14.10 0000000014.10 werden.

Die neue SP:

[EDIT] so ist korrigiert...[/EDIT]

Code:

SET TERM ^ ;

create or alter procedure sp_sortstring (
    wert varchar(20))
returns (
    result varchar(30))
as
declare variable laenge smallint;
declare variable pos smallint;
declare variable zeichen char(1);
declare variable temp varchar(20);
declare variable s1 varchar(10);
begin
  result = null;
  laenge = char_length(:wert);
  if (:laenge > 0) then
  begin
    pos = 1;
    while (:pos <= :laenge) do
    begin
      zeichen = substring(:wert from :pos for 1);
      if (:zeichen in ('0','1','2','3','4','5','6','7','8','9', '.')) then
        temp = coalesce(:temp, '')||:zeichen;
      else
        break;
      pos = :pos + 1;
    end 
  end
  if (coalesce(:temp, '') <> '') then
  begin
    -- Zahl vor Punkt auffüllen
    pos = position('.', :temp);
    if (:pos = 0) then
      result = lpad(:temp, 10, '0');
    else
      result = lpad(substring(:temp from 1 for :pos -1), 10, '0')||'.'||lpad(substring(:temp from :pos + 1 for :laenge), 10, '0');
  end
  suspend;
end^

SET TERM ; ^

Ob die 10 in der SP für lpad reicht, musst du wissen.
Anpassen ist ja kein Problem.
Musst im Select natürlich den geänderten SP-Name benutzen...

Musst du öfters viele 1000 Datensätze sortieren, kann das natürlich langsam werden.
Dann sollte man darüber nachdenken, ein indexiertes Feld mitzuführen, welches in einem Trigger (Bevor Insert + Update) aktualisiert wird.

Frank

[Walter Landwehr]

Hallo Frank,

ein indiziertes Feld habe ich sowieso angelegt.

Leider liefert die SP bei

2a und 2b den gleichen Wert, damit kann die Sortierung fehlerhaft werden.

2b
2a

richtig wäre aber

2a
2b

hast Du hierzu noch eine Idee.

Ansonsten wäre die SP perfekt.

[TBx]

Davon ausgehend, dass Buchstaben immer nur ein Buchstabe hinten angehängt sein kann, kann man diesen beim ermitteln des "Dezimalanteils" ausschneiden und an das Result wieder ankleben. Dann wäre das in der Sortierung sauber mit drin.

Code:

SET TERM ^ ;

create or alter procedure sp_sortstring (
    wert varchar(20))
returns (
    result varchar(30))
as
declare variable laenge smallint;
declare variable pos smallint;
declare variable zeichen char(1);
declare variable temp varchar(20);
declare variable s1 varchar(10);
declare variable s2 varchar(10);
begin
  s2='';
  result = null;
  laenge = char_length(:wert);
  if (:laenge > 0) then
  begin
    pos = 1;
    while (:pos <= :laenge) do
    begin
      zeichen = substring(:wert from :pos for 1);
      if (:zeichen in ('0','1','2','3','4','5','6','7','8','9', '.')) then
        temp = coalesce(:temp, '')||:zeichen;
      else
        s2 = :zeichen;
        break;
      pos = :pos + 1;
    end 
  end
  if (coalesce(:temp, '') <> '') then
  begin
    -- Zahl vor Punkt auffüllen
    pos = position('.', :temp);
    if (:pos = 0) then
      result = lpad(:temp, 10, '0');
    else
      result = lpad(substring(:temp from 1 for :pos -1), 10, '0')||'.'||lpad(substring(:temp from :pos + 1 for :laenge), 10, '0');
  end
  result = :result || :s2;
  suspend;
end^

SET TERM ; ^

Das ist jetzt nur mal auf die Schnelle eingeflickt, ungetestet.

[Walter Landwehr]

Hallo Herr Breitkreuz,

Das ist nun völlig falsch, Sortierung ergibt:
1
20.2
20.3
2a
2b
33.1
5
9.1
12.1
12.10
12.7

richtig wäre:
1
2a
2b
5
12.1
12.7
12.10
20.2
20.3
33.1

[dataspider]

Dann muss der Rest hinten ran:

[EDIT]SP - Code noch mal geändert[/EDIT]

Code:

SET TERM ^ ;

create or alter procedure sp_sortstring (
    wert varchar(20))
returns (
    result varchar(30))
as
declare variable laenge smallint;
declare variable pos smallint;
declare variable zeichen char(1);
declare variable temp varchar(20);
declare variable s1 varchar(10);
declare variable rest varchar(10);
begin
  result = null;
  laenge = char_length(:wert);
  if (:laenge > 0) then
  begin
    pos = 1;
    while (:pos <= :laenge) do
    begin
      zeichen = substring(:wert from :pos for 1);
      if (:zeichen in ('0','1','2','3','4','5','6','7','8','9', '.')) then
        temp = coalesce(:temp, '')||:zeichen;
      else
      begin
        -- Rest hinten
        rest = substring(:wert from :pos for 10);
        break;
      end 
      pos = :pos + 1;
    end 
  end
  if (coalesce(:temp, '') <> '') then
  begin
    -- Zahl vor Punkt auffüllen
    pos = position('.', :temp);
    if (:pos = 0) then
      result = lpad(:temp, 10, '0');
    else
      result = lpad(substring(:temp from 1 for :pos -1), 10, '0')||'.'||
               lpad(substring(:temp from :pos + 1 for :laenge), 10, '0');
    result = :result||coalesce(:rest, '');
  end
  suspend;
end^

SET TERM ; ^

Wenn dann allerdings weitere zahlen kommen, dann sollte man wirklich über Alternativen nachdenken.
Die Länge der Varchars musst du noch prüfen. Ich weiß ja nicht, wie lang die Werte bei dir werden können.

Ach ja, wenn 2a vor 2.10a kommt (siehe Anhang), dann musst du wie folgt ändern:

Code:

  ...
  if (coalesce(:temp, '') <> '') then
  begin
    -- Zahl vor Punkt auffüllen
    pos = position('.', :temp);
    if (:pos = 0) then
      result = lpad(:temp, 10, '0')||'.'||lpad('0', 10, '0');
    else
      result = lpad(substring(:temp from 1 for :pos -1), 10, '0')||'.'||
               lpad(substring(:temp from :pos + 1 for :laenge), 10, '0');
    result = :result||coalesce(:rest, '');
  end

Frank

[Walter Landwehr]

Hallo Frank,

Danke das sieht perfekt aus.