Zahlen im Varchar2-Feld korrekt sortieren. Geht das?

[meute]

Hallo,

gibt es eine Möglichkeit, Zahlen im Varchar2-Feld korrekt zu sortieren?

Im Moment werden die Felder so sortiert:
1
10
11
11 a
2
20
21
22 a

Sie sollen aber so sortiert werden:
1
2
10
11
11 a
20
21
22 a

Die Buchstaben bei den Zahlen sind korrekt. Es geht hier um Hausnummern.
Der Feldtyp kann nicht geändert werden.

Hat jemand einen Tipp für mich?

Danke.


Gruß
meute

[uminky]

So, das sollt es sein:

WITH temp AS(SELECT '1' hnr FROM dual UNION ALL
SELECT '2' hnr FROM dual UNION ALL
SELECT '10' hnr FROM dual UNION ALL
SELECT '11' hnr FROM dual UNION ALL
SELECT '11 a' hnr FROM dual UNION ALL
SELECT '20' hnr FROM dual UNION ALL
SELECT '21' hnr FROM dual UNION ALL
SELECT '22 a' hnr FROM dual
)
SELECT hnr
FROM temp
ORDER BY LPAD( NVL(SUBSTR(hnr,1, INSTR(hnr,' ')-1 ),hnr) ,10,'0')
, hnr
/

[ebis]

ORDER BY LEN (Column), Column)

[meute]

Hallo,

So, das sollt es sein:

WITH temp AS(SELECT '1' hnr FROM dual UNION ALL
SELECT '2' hnr FROM dual UNION ALL
SELECT '10' hnr FROM dual UNION ALL
SELECT '11' hnr FROM dual UNION ALL
SELECT '11 a' hnr FROM dual UNION ALL
SELECT '20' hnr FROM dual UNION ALL
SELECT '21' hnr FROM dual UNION ALL
SELECT '22 a' hnr FROM dual
)
SELECT hnr
FROM temp
ORDER BY LPAD( NVL(SUBSTR(hnr,1, INSTR(hnr,' ')-1 ),hnr) ,10,'0')
, hnr
/

@uminky
Danke für Deine Antwort.
Aber so wie ich Deine Lösung sehe, muss für jeden Eintrag eine Code-Zeile erstellt werden.

Deine Lösung ist deshalb aus zwei Gründen nicht möglich.

1. Es können immer wieder Daten hinzukommen, die bisher nicht beachtet wurden.
2. Leider sind das ja nur ein paar Beispieldaten, die ich gepostet habe. Aktuell sind 1.070 verschiedene Hausnummern drin bei gesamt 30.060 Datensätzen.


Gruß
meute

[meute]

Hallo,

ORDER BY LEN (Column), Column)

Danke für Deine Antwort.

Deine Syntax scheint nicht für Oracle zu sein.

In Oracle funktioniert es mit dieser Syntax:
order by LENGTH (COLUMN) asc, COLUMN asc

Ergebnis:
1
2
10
11
20
21
1 a
1 b
2 c
2 d

Wenn keine Leerzeichen in den Daten wären, würde Dein Lösung perfekt funktionieren.
Aber die Zahlen mit Leerzeichen und Buchstaben stehen am Ende.

Wunsch:
1
1 a
1 b
2
2 c
2 d
10
11
20
21

Evtl. könnte es so gehen:
Es müsste ermittelt werden, ob ein Leerzeichen drin ist. Wenn ja, dann nur den Teil vor dem Leerzeichen brücksichtigen.

Hast Du oder hat jemand dazu eine Idee, dies zu realliseren?

Danke.


Gruß
meute

[ebis]

so?

ORDER BY 
--weg mit den Buchstaben und Leerzeichen
RTRIM (REPLACE (TRANSLATE (UPPER (HNR), 'ABCDEFGHIJKLMNPQRSTUVWXYZ', '#','')) ,
--weg mit den Zahlen
REPLACE (TRANSLATE (HNR), '0123456789', '#',''))

[uminky]

das WITH temp AS ( ... ) ist nur für den Test, um Daten zum Testen zu haben.
Du brauchst nur das: ORDER BY ... , wo bei hnr das Feld der Hausnummer ist.
Wenn du bei einigen Datensätzen kein Leerzeichen vor dem Buchstaben hast, musst du es mit dem 'Translate' von ebis kombinieren. Das Translate benötigt in diesem Fall so viele Leerzeichen in der zweiten Klammer, wie du Zeichen in der erste hast (der dritte Parameter ist falsch):
TRANSLATE (HNR), 'ABCDEFGH', ' ')). Ich habe deinen Beispielen noch ein paar hinzugefügt. Aus denen du erkennen kannst, dass du noch ein paar Zeichen mehr erseten musst ( - / , .. )


edit:
die Leerzeichen werden von der Forumsoftware weggekürzt

WITH temp AS(SELECT '1' hnr FROM dual UNION ALL
SELECT '2' hnr FROM dual UNION ALL
SELECT '10' hnr FROM dual UNION ALL
SELECT '11' hnr FROM dual UNION ALL
SELECT '11-15' hnr FROM dual UNION ALL
SELECT '11 a' hnr FROM dual UNION ALL
SELECT '20' hnr FROM dual UNION ALL
SELECT '21' hnr FROM dual UNION ALL
SELECT '21d' hnr FROM dual UNION ALL
SELECT '21c' hnr FROM dual UNION ALL
SELECT '22 a' hnr FROM dual
)
SELECT hnr
, TRANSLATE (UPPER (HNR), 'ABCDEFGHIJKLMNPQRSTUVWXYZ-', ' ') t
, LPAD( NVL(SUBSTR(hnr,1, INSTR(hnr,' ')-1 ),hnr) ,10,'0') r
, LPAD( NVL(SUBSTR(TRANSLATE (UPPER (HNR), 'ABCDEFGHIJKLMNPQRSTUVWXYZ-/', ' '),1, INSTR(TRANSLATE (UPPER (HNR), 'ABCDEFGHIJKLMNPQRSTUVWXYZ-/', ' '),' ')-1 ),TRANSLATE (UPPER (HNR), 'ABCDEFGHIJKLMNPQRSTUVWXYZ-/', ' ')) ,10,'0') r
FROM temp
ORDER BY 4,1
/

[meute]

Hallo,

@ebis
@uminky

Danke für Eure Mühe.

Ich habe dank Euren Anregungen und dem Vorschlag "ORDER BY LEN (Column), (Column)" von ebis (Post #3) heute vormittag selbst gebastelt und bin selbst zu dieser Lösung gekommen:

order by length(trim(substr(COLUMN, 1, instr(rpad(COLUMN,20,' '),' ')))) asc, COLUMN asc

-- Beschreibung obiger "order by"-Klausel:
-- rpad füllt alle Hs.-Nr. bis zur Gesamtlänge von 20 Zeichen rechts mit Leerzeichen auf.
-- instr sucht die Position des ersten Leerzeichen.
-- substr liest den Text bis zum ersten Leerzeichen.
-- trim entfernt alle Leerzeichen am Text.
-- length ermittelt die Länge vom Text.


Das hier würde auch funktionieren, setzt aber voraus, dass alle Hs.-Nr. korrekt erfasst sind und nach den Zahlen immer ein Leerzeichen kommt.

order by to_number(trim(substr(COLUMN, 1, instr(rpad(COLUMN,20,' '),' ')))) asc, COLUMN asc


Gruß
meute

[uminky]

genau, und dafür ist das Translate. Um für das Leerzeichen zu sorgen, damit das substr/instr die erste Zahl heraus trennen kann.

[meute]

Hallo,

WITH temp AS(SELECT '1' hnr FROM dual UNION ALL
SELECT '2' hnr FROM dual UNION ALL
SELECT '10' hnr FROM dual UNION ALL
SELECT '11' hnr FROM dual UNION ALL
SELECT '11-15' hnr FROM dual UNION ALL
SELECT '11 a' hnr FROM dual UNION ALL
SELECT '20' hnr FROM dual UNION ALL
SELECT '21' hnr FROM dual UNION ALL
SELECT '21d' hnr FROM dual UNION ALL
SELECT '21c' hnr FROM dual UNION ALL
SELECT '21 1/2' hnr FROM dual UNION ALL
SELECT '25 /3' hnr FROM dual UNION ALL
SELECT '22 a' hnr FROM dual
)
SELECT hnr
, TRANSLATE (UPPER (HNR), 'ABCDEFGHIJKLMNPQRSTUVWXYZ-_/', ' ') t
, LPAD( NVL(SUBSTR(hnr,1, INSTR(hnr,' ')-1 ),hnr) ,10,'0') r
, LPAD( NVL(SUBSTR(TRANSLATE (UPPER (HNR), 'ABCDEFGHIJKLMNPQRSTUVWXYZ-_/', ' '),1, INSTR(TRANSLATE (UPPER (HNR), 'ABCDEFGHIJKLMNPQRSTUVWXYZ-_/', ' '),' ')-1 ),TRANSLATE (UPPER (HNR), 'ABCDEFGHIJKLMNPQRSTUVWXYZ-_/', ' ')) ,10,'0') r
FROM temp
ORDER BY 4,1
/

@uminky
Ich habe nun meine "order by"-Klausel noch mal Schritt für Schritt umgebaut und translate eingebaut. Sorry, aber ich will verstehen, was da genau passiert.

order by length(nvl(trim(substr(rpad(ltrim(translate(upper( HNR), 'ABCDEFGHIJKLMNPQRSTUVWXYZ-_/',' ')),20,' '), 1, instr(rpad(ltrim(translate(upper(HNR), 'ABCDEFGHIJKLMNPQRSTUVWXYZ-_/',' ')),20,' '),' '))),0)) asc, HNR asc

-- Beschreibung obiger "order by"-Klausel von innen nach außen:
-- translate ersetzt alle angegebenen Zeichen durch Leerzeichen (doppelt vorhanden für substr).
-- ltrim entfernt linke Leerzeichen (doppelt vorhanden für substr).
-- rpad füllt alle Hs.-Nr. bis zur Gesamtlänge von 20 Zeichen rechts mit Leerzeichen auf (doppelt vorhanden für substr).
-- instr sucht die Position des ersten Leerzeichen.
-- substr liest den Text bis zum ersten Leerzeichen.
-- trim entfernt am Text alle noch vorhandenen Leerzeichen.
-- nvl ersetzt NULL-Werte durch 0.
-- length ermittelt die Länge vom Text.


Es gibt aber ein Problem.
Ich habe bei translate noch das ersetzen der Zeichen Unterstrich "_" und Slash "/" eingebaut.
Aber leider ersetzt translate die Zeichen "-", "_" und "/" nicht durch Leerzeichen sondern entfernt diese Zeichen.

Warum ist das so und kann man den Fehler beheben?


Danke.


Gruß
meute

[meute]

Hallo,

Es gibt aber ein Problem.
Ich habe bei translate noch das ersetzen der Zeichen Unterstrich "_" und Slash "/" eingebaut.
Aber leider ersetzt translate die Zeichen "-", "_" und "/" nicht durch Leerzeichen sondern entfernt diese Zeichen.

Warum ist das so und kann man den Fehler beheben?

OK, ich hab's selbst herausgefunden.
Beim translate müssen die Leerzeichen in der gleichen Anzahl vorhanden sein wie die Anzahl der zu ersetzenden Zeichen.


Gruß
meute

[uminky]

Du musst als dritten Parameter so viele Leerzeichen angeben wie du Zeichen zum suchen (2.Parameter) angibst.
Die Forumsoftware lässt die wiederholten Leerzeichen weg. Darum zwei Beispiele mit X:

select translate('abcdefg','cdef','XXXX') from dual;
abXXXXg

select translate('abcdefg','cdef','XXX') from dual;
abXXXg