[SQL] Sorteren op huisnummers

[Verwijderd]

Huidige situatie: MSSQL database en een tabel met adressen. Het is de bedoeling dat ik na het uitvoeren van een bepaalde query sorteer op huisnummer. Echter ziet deze kolom er als volgt uit:

1
10
100
101
102
104a
2
21
201
222
222ii

Het gaat om een varchar veld. Als je dit sorteert krijg je dus de output hierboven: 100 komt eerder dan 2 etc. Dat is niet de bedoeling. Ik wil 1 als eerst en 222 als laatst. Dit valt op te lossen door SELECT CAST(huisnummer as INT) te doen, maar dit is niet mogelijk gezien het feit dat er ook nummers als '104a' en '222ii' bij zitten.

Een oplossing hiervoor zou zijn om met behulp van een scriptje de nummers van hun postfix te strippen en deze prefix in een aparte kolom op te slaan, en daar op te sorteren. Maar we krijgen dit bestand eens in de zoveel tijd van een externe partij aangeleverd en we willen dus eigenlijk niets aan het formaat veranderen. Weet iemand een manier om dit wel te sorteren met evt. behulp van wat slimme (MS)SQL functies?

[ Voor 4% gewijzigd door Verwijderd op 10-03-2003 14:53 ]

[Verwijderd]

Als je een importscriptje schrijft, kun je toch elke keer de prefixen (sp?) er elke keer automatisch tijdens het importeren uitslopen, of importeer je elke keer met de hand?

Een scriptje zou karakter voor karakter moeten kijken of het een cijfer danwel letter is en aan de hand daarvan moeten gaan opsplitsen... lijkt me een tikje kostbare oplossing in een SQL-query.

[ Voor 38% gewijzigd door Verwijderd op 10-03-2003 14:49 ]

[Freee!!]

Ik denk dat je toch huisnummer en toevoeging zult moeten scheiden.

offtopic:
Die toevoeging aan het huisnummer is in ieder geval voor de gegeven voorbeelden een postfix, niet een prefix (komt erna, niet ervoor)

Je kan natuurlijk die toevoeging wel meenemen als sorteercriterium als je het een apart veld maakt.

offtopic:
Ik wordt net in een privé mailtje van een modje ervan op de hoogte gesteld dat de correcte benaming 'suffix' is. Ik was in de war met prefix, infix en postfix notaties bij berekeningen.

[ Voor 28% gewijzigd door Freee!! op 10-03-2003 15:21 . Reden: SUFFIX ]

[beetle71]

Gaat dit niet?

SELECT huisnummers as a, CAST (huisnummer as INT) FROM table ORDER by huisnummer,a

Edit: Nee, werkt niet...

[ Voor 13% gewijzigd door beetle71 op 10-03-2003 15:09 ]

[Verwijderd]

Ik heb opgelost door zelf een funtie te schrijven:

code:

CREATE FUNCTION dbo.fn_onlynumeric
  (@StrVal AS VARCHAR(8000))
RETURNS VARCHAR(8000
AS
BEGIN
  WHILE PATINDEX('%[^0-9]%', @StrVal) > 0
    SET @StrVal = REPLACE(@StrVal,
        SUBSTRING(@StrVal,
        PATINDEX('%[^0-9]%', @StrVal),1),'')
 RETURN @StrVal
END

En de query aan te passen:

code:

SELECT huisnummer FROM adres
ORDER by cast(dbo.fn_onlynumeric(hsnr) as int)

Response tijd is acceptabel:

code:

Time=47ms, Records=543

[ Voor 5% gewijzigd door Verwijderd op 10-03-2003 15:10 ]

[Freee!!]

Nu de suffixen nog goed sorteren bij gelijke huisnummers

[Verwijderd]

lol!

Wil jij eens even niet zo lastig doen!

[Freee!!]

Verwijderd schreef op 10 maart 2003 @ 15:35:
lol!

Wil jij eens even niet zo lastig doen!

Ik ben helemaal niet lastig, is een standaard gebruikersverzoek (bittere ervaring )
Echt lastig wordt het als je adressen in Haarlem erbij hebt (toevoegingen als 'rood' en 'zwart')

[Verwijderd]

Ik weet het. Het gaat hier om een bestand van circa 8 miljoen records dus ik heb al een paar bizarre zaken voorbij zien komen Mijn gelukje is dat die sortering al is gebeurd omdat de records op postfix niveau wel in de goede volgorde zijn geimporteerd.

[beetle71]

Volgens mij is het op suffix sorteren niet zo moeilijk....

SELECT huisnummer FROM adres
ORDER by cast(dbo.fn_onlynumeric(hsnr) as int), huisnummer

ik bedoel als ze 'INT'-matig gelijk zijn kun je ze verder toch subsorteren als strings..

of maak ik nu een denkfout?

[Verwijderd]

Klopt ja, dat werkt prima! Is zelfs nog beter voor het geval onze leverancier ooit besluit het niet meer gesorteerd aan te leveren!

[MSalters]

Rickman: IVP database bestand?

[ACM]

't Grootste nadeel hiervan is dat je waarsch niet de index erop kan gebruiken bij sorteringen enzo, door die functie in de order.
Als de tabel klein is of er weinig eisen aan gesteld worden zal dit wel aardig werken verder.

[pkouwer]

Ik heb ook wel eens zoiets gemaakt, een importtool om van meerdere docmenten willekeurig te importeren. Waar je ook op moet letten is straten zoals Plein 1940 3a 2hoog