Strings mittel ORDER BY richtig numerisch sortieren

[Andre Ladig]

Hallo,
Ich habe folgendes Problem. Ich muss in einer Datenbank Anschriften nach Hausnummern sortieren. Das Feld für die Hausnummer ist varchar, da z.B. auch "30a" möglich ist. Wenn ich

SELECT * FROM Anschrift ORDER BY Hausnummer
laufen lasse wird wird ja richtigerweise alphanumerisch sortiert
also
1
10
2
3
30
3a
...

wie kann ich es anstellen, das sie Sortierung numerisch richtig erfolgt

Grüße

Andre

[Ralf Jansen]

Da das Problem und die Lösung, wie so oft, Datenbank abhängig ist und du nicht gesagt hast welche DB du benutzt geh ich jetzt einfach mal davon das es um den SQL Server geht.
Falls nicht Pech gehabt

Man muß den Ausdruck in ORDER BY so formatieren das er richtig sortiert wird. Im simplen Fall, die Hausnummer ist wirklich numerisch, kann man einfach konvertieren.

SELECT * FROM Anschrift (nolock) ORDER BY CAST(Hausnummer as integer)

Das hilft dir natürlich nicht bei der Hausnummer '3a'.
Zweite möglich wäre die Hausnummern mit führenden 0'en aufzüfullen und dann zu sortiern.

SELECT * FROM Anschrift (nolock) ORDER BY RIGHT('0000000000' + Hausnummer ,10)

Dann ist die Hausnummer '3a' aber immer noch hinter der '30'.

Die dritte Möglichkeit ist ,da du ja genaugenommen weder eine nummerische noch eine alphanummerische Sortierung brauchst sondern eine die der üblichen Reihenfolge der Hausnummer auf deutschen Strassen entspricht, eine Stored Function zu schreiben die dir die Hausnummer so formatiert das sie wunschgemäß sortiert werden kann.

CREATE FUNCTION fn_HausnummerSortableValue(@Hausnummer varchar(10))
RETURNS varchar(20)
AS
BEGIN
DECLARE @Result varchar(20),
@Char varchar(1),
@CharIndex int;
IF LEN( @Hausnummer ) > 0
BEGIN
-- nummerischen Anteil nach @Result kopieren
SET @Result = 0;
SET @CharIndex = 1;
SET @Char = SUBSTRING ( @Hausnummer , @CharIndex , 1 )
WHILE (@Char like '[0123456789]') AND (@CharIndex <= LEN(@Hausnummer))
BEGIN
SET @Result = @Result + @Char;
SET @CharIndex = @CharIndex + 1;
SET @Char = SUBSTRING ( @Hausnummer , @CharIndex , 1 )
END
-- alphanummerischen Teil anhängen und alles auf gleiche Länge bringen
SET @Result = RIGHT('0000000000' + @Result,10) + RIGHT('0000000000' + @Hausnummer,10)
END
RETURN(@Result)
END

dann sollte ein

SELECT * FROM Anschrift (NOLOCK) ORDER BY dbo.fn_HausnummerSortableValue(Hausnummer)

die gewünschte Reihenfolge liefern.

Gruß
Ral

[Ralf Jansen]

Sorry für die Formatierung.
Diese dämliche Eingabebox schluckt meine Leerzeilen.

Ral

[Uschi Blanz]

Hallo Andre,

bei Oracle geht das ganz einfach:

2 Teilstrings bilden. Im ersten nur die Ziffern, mit führenden 0 bis zur maximallänge der Spalte auffüllen. Im zweiten die Buchstaben. Das ganze anerinander hängen zum Sortieren:

select * from Anschrift order by lpad(translate(upper(Hausnummer), '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789'), 10, '0') || translate(upper(Hausnummer), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ');

mal davon ausgehend, dass immer vorne Ziffern und danach nur noch Buchstaben kommen, sonst wird das Auseinandernehmen und Zusammensetzen länger.

Gruß
Usch

[Andre Ladig]

Hallo Ralf und Uschi,
erst mal Danke für Eure Antworten. Da ich aber das Wochenende nicht am rechner war kann ich mich erst jetzt mit Euren Antworten befassen.
Also ich benutze MS-SQL 2000 und werde also Ralf Lösung befassen.
Ich melde mich dann wieder wenns klappt.
Gruß

Andr

[Andre Ladig]

Hallo Ralf,
Danke klappt vorzüglich!
Gruß
Andr

[Olaf Steffan]

Hallo Andre,<BR><BR>ich habe heute erst von deinem Problem gelesen. Ich hätte da auch eine Idee ohne UDF, da bei größeren Ergebnismengen die Abfragen doch etwas träge werden können.<BR>Die Idee basiert also auf der Funktion PATINDEX mit der man "weich" suchen kann. Allerdings muss auch hier erst der numerische und dann der alphanumerische Teil stehen:<BR><BR>
create table #a (a varchar(10))
--
insert into #a values ('1')
insert into #a values ('10')
insert into #a values ('2')
insert into #a values ('3')
insert into #a values ('30')
insert into #a values ('3a')
--
select * from #a order by a --bäh
--
select *
from #a
order by --!
cast(left( a, case when patindex('%[a-z]%', a) = 0 then len(a) else patindex('%[a-z]%', a) - 1 end) as int)
,case when patindex('%[a-z]%', a) = 0 then '' else substring(a, patindex('%[a-z]%', a), len(a)) end
<BR>
Viele Grüße Ola

[Jan Hermes]

hallo

vor so einem problem stehe ich gerade auch allerdings mit advantage database server als datenbank

gibts es da eine ähnliche lösung?

bei mir sinds nur einzelne positionsnummer die ich ordnen möchte.

gruß ja