[SQL] Nummers 'netjes' sorteren in varchar kolom

[Polderdijk]

Ik ben een aantal weken geleden overgestapt van Acces naar Microsoft SQL server 2000 en heb dus heel mijn ASP applicatie omgebouwd om met SQL te werken, echter heb ik alleen 1 probleem wat wél met Access werkte en niet met SQL.

Als ik een ORDER BY Subnummer geeft dan krijg ik:

code:

1.00
10.00
11.00
12.05
13.10
14.00
15.50
16.00
17.10
18.10
19.10
19.11
19.12
2.00
20.00
21.00
..
29.10
3.10
30.00

Echter wil ik hebben:

code:

1.00
2.00
3.10
10.00
11.00
12.05
13.10
14.00
15.50
16.00
17.10
18.10
19.10
19.11
19.12
20.00
21.00
..
29.10
30.00

In Access heb ik het probleem opgelost door ([rml]Crazy D in "[ ASP] Speciaal ORDER functie"[/rml]) met de code: Right(Space(10) & SubNummer, 10) echter SQL slikt dit niet, en krijg de volgende foutcode:
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid operator for data type. Operator equals boolean AND, type equals varchar

Hoe kan ik er voor zorgen dat tóch de data netjes wordt gesorteerd?

[gorgi_19]

1. Alles converten naar een float
2. In je functie / sortfunctie de boel CASTen of CONVERTen naar een float.

[OZ-Gump]

Sorteer op de value van het veld, in plaats van de tekst die er in staat. (dmv VAL(Veldnaam)) Ik weet alleen niet hoe goed MSSQL daarmee om gaat...

[Jochem Knoops]

ik denk dat je de structuur van de database verkeerd hebt als je het veld van subnummer als tekstveld hebt aangegeven dan zal hij sorteren alsof het een tekst is en als je het als getal hebt zal hij dus sorteren als getal.

[OZ-Gump]

JochemKnoops schreef op 02 oktober 2003 @ 11:55:
ik denk dat je de structuur van de database verkeerd hebt als je het veld van subnummer als tekstveld hebt aangegeven dan zal hij sorteren alsof het een tekst is en als je het als getal hebt zal hij dus sorteren als getal.

Daar ben ik het dan weer helemaal mee eens... Maar dat is niet de vraag hier Tenzij de TS de mogelijkheid (nog) heeft veranderingen aan te brengen in zijn databasemodel??? (open vraag! )

Anders zul je inderdaad met een cast, een convert of misschien val moeten werken...

[Jochem Knoops]

als hij de cenversie van acces -> sql server niet goed gedaan heeft (standaard staan alle waardes op tekst volgens mij) dan zou dit een probleem zijn.

[Jaspertje]

Mag ik vragen waarom je van de tabel een varchar hebt gemaakt, en geen decimal?

[OZ-Gump]

JochemKnoops schreef op 02 oktober 2003 @ 12:01:
als hij de cenversie van acces -> sql server niet goed gedaan heeft (standaard staan alle waardes op tekst volgens mij) dan zou dit een probleem zijn.

offtopic:
in de openingspost staat duidelijk dat hij ditzelfde probleem had in Access. Er staat zelfs een link bij naar het topic waarin de oplossing voor het probleem in Access staat. En er staat ook bij dat die oplossing niet werkt in SQL.

Heeft de TS al een oplossing?

[Polderdijk]

JochemKnoops schreef op 02 oktober 2003 @ 11:55:
ik denk dat je de structuur van de database verkeerd hebt als je het veld van subnummer als tekstveld hebt aangegeven dan zal hij sorteren alsof het een tekst is en als je het als getal hebt zal hij dus sorteren als getal.

Nee, ik heb bewust voor varchar gekozen doordat onderstaande varianten in het veld kunnen staan:

01.00
100
100q
2.25v
open
etc. etc.

[Jaspertje]

OZ-Gump schreef op 02 oktober 2003 @ 12:09:
[...]

offtopic:
in de openingspost staat duidelijk dat hij ditzelfde probleem had in Access. Er staat zelfs een link bij naar het topic waarin de oplossing voor het probleem in Access staat. En er staat ook bij dat die oplossing niet werkt in SQL.

Heeft de TS al een oplossing?

Een oplossing zou dus zijn om het type in je tabel te veranderen. Volgens mij is dit ook de beste oplossing en de netsje.. Tuurlijk zou je alles in een Array kunnen zetten, dan de lengte bekijken en daarna nog eens gaan sorteren, maar daar wordt je ook niet vrolijk van denk ik.. Dus ja de TS heeft al meerdere oplossingen gekregen.....

edit:

Nu dus niet meer

[ Voor 3% gewijzigd door Jaspertje op 02-10-2003 12:32 ]

[Polderdijk]

OZ-Gump schreef op 02 October 2003 @ 11:51:
Sorteer op de value van het veld, in plaats van de tekst die er in staat. (dmv VAL(Veldnaam)) Ik weet alleen niet hoe goed MSSQL daarmee om gaat...

Helaas gaat dit niet, krijg de foutmelding: 'VAL' is not a recognized function name.

[OZ-Gump]

Polderdijk schreef op 02 oktober 2003 @ 12:32:
[...]
Nee, ik heb bewust voor varchar gekozen doordat onderstaande varianten in het veld kunnen staan:

01.00
100
100q
2.25v
open
etc. etc.

Kijk, als je dat nou eerder had gezegd hadden we een stuk eerder een paar andere antwoorden kunnen laten varen. In je openingspost staan namelijk alleen numerieke waarden in het rijtje.

Mijn tip: zorg (bij het posten in de tabel) dat elke numerieke waarde altijd een lengte heeft van 6. Dat wil zeggen:
• 3 karakters voor de punt
• de punt zelf
• twee karakters na de punt

Op die manier kun je 'gewoon' sorteren op het veld en heb je altijd een juiste sortering. Als je namelijk zorgt voor 009.15 in plaats van 9.15 kom je er altijd! Als je tenminste genoeg hebt aan 1000 gehele getallen

[curry684]

Dit is natuurlijk een ranzige oplossing van hier tot Tokio, je moet gewoon 2 kolommen float en varchar maken, met de tekst als leading. Indien de varchar NULL bevat ga je de float uitlezen, en klaar is Klara en alle dubieuze problemen.

[OZ-Gump]

curry684 schreef op 02 October 2003 @ 12:40:
Dit is natuurlijk een ranzige oplossing van hier tot Tokio, je moet gewoon 2 kolommen float en varchar maken, met de tekst als leading. Indien de varchar NULL bevat ga je de float uitlezen, en klaar is Klara en alle dubieuze problemen.

Ook hier weer volledig mee eens. Ik probeerde alleen een oplossing te vinden voor een bestaand probleem zonder aan het databasemodel te gaan knutselen.
In een van mijn eerdere posts heb ik ook al aangegeven dat een aanpassing van het databasemodel m.i. beter is.

[Polderdijk]

OZ-Gump schreef op 02 October 2003 @ 12:36:
[...]
Kijk, als je dat nou eerder had gezegd hadden we een stuk eerder een paar andere antwoorden kunnen laten varen. In je openingspost staan namelijk alleen numerieke waarden in het rijtje.

Mijn excuses, maar ik dacht een simpel voorbeeld te geven, maar het was dus iets té simpel

Mijn tip: zorg (bij het posten in de tabel) dat elke numerieke waarde altijd een lengte heeft van 6. Dat wil zeggen:
• 3 karakters voor de punt
• de punt zelf
• twee karakters na de punt

Op die manier kun je 'gewoon' sorteren op het veld en heb je altijd een juiste sortering. Als je namelijk zorgt voor 009.15 in plaats van 9.15 kom je er altijd! Als je tenminste genoeg hebt aan 1000 gehele getallen

zoals curry684 zegt ben ik niet echt zo blij met die oplossing! Ik ga ff kijken wat dat floating inhoud!

[Polderdijk]

OZ-Gump schreef op 02 October 2003 @ 12:45:
[...]
Ook hier weer volledig mee eens. Ik probeerde alleen een oplossing te vinden voor een bestaand probleem zonder aan het databasemodel te gaan knutselen.
In een van mijn eerdere posts heb ik ook al aangegeven dat een aanpassing van het databasemodel m.i. beter is.

En helaas heb ik niets in te brengen in de databasemodel omdat hier al voor gekozen is sinds ons bedrijf in database werken en heb dus de gehele history geimporteerd, dus eerst van dbase III naar Access, van Acces weer naar SQL en er bestaat dus geen enkele mogelijkheid om dit aan te passen, ook omdat de ordernummer en subnummer, waar het hier dus omgaat, de belangrijkste knooppunten zijn om elk rapport/factuur/overzichten enzo uit te draaien, dus ik kan en mag hier dus niks in wijzigen....

[Polderdijk]

gorgi_19 schreef op 02 oktober 2003 @ 11:51:
1. Alles converten naar een float
2. In je functie / sortfunctie de boel CASTen of CONVERTen naar een float.

Kan je misschien wat meer informatie hierbij vertellen wat dat nou precies inhoud, want ik snap er eigenlijk weinig van wat je precies bedoeld!

[OZ-Gump]

Een float is een double is een numerieke weergave van je gegevens. En dat gaat dus (blijkbaar) niet lukken omdat je ook alfanumerieke waarden in je veld hebt staan. Tenzij je door middel van een algoritme eerst alleen de numerieke waarden uit elk veldje peutert en er vervolgens een float van maakt, maar dat wordt ook weer moeilijk. Dus misschien is het even lang maken van elk veld dan toch de oplossing?

[Polderdijk]

OZ-Gump schreef op 02 October 2003 @ 13:13:
Een float is een double is een numerieke weergave van je gegevens. En dat gaat dus (blijkbaar) niet lukken omdat je ook alfanumerieke waarden in je veld hebt staan. Tenzij je door middel van een algoritme eerst alleen de numerieke waarden uit elk veldje peutert en er vervolgens een float van maakt, maar dat wordt ook weer moeilijk. Dus misschien is het even lang maken van elk veld dan toch de oplossing?

Kijk, dat houd float dus eigenlijk in...

Maar ik denk dat ik het maar laat zoals het is, het is lastig voor de gebruikers, maar kan er weinig aan doen dus.

De optie om de subnummers te veranderen door bijvoorbeeld 0-en ervoor te zetten enzo kan en mag ik absoluut niet omdat dit dé koppelingen is binnen onze systemen/bedrijf en bij al onze klanten en het is dus absoluut NIET toegestaan om daar dus aanpassingen in te maken omdat dit dan ook terug gekoppeld moet worden naar de klanten toe (momenteel 90 klanten met 8990 subnummers!!!) en de Holding die daar écht niet blij van wordt.

[gorgi_19]

Polderdijk schreef op 02 October 2003 @ 13:35:
[...]

Kijk, dat houd float dus eigenlijk in...

Maar ik denk dat ik het maar laat zoals het is, het is lastig voor de gebruikers, maar kan er weinig aan doen dus.

De optie om de subnummers te veranderen door bijvoorbeeld 0-en ervoor te zetten enzo kan en mag ik absoluut niet omdat dit dé koppelingen is binnen onze systemen/bedrijf en bij al onze klanten en het is dus absoluut NIET toegestaan om daar dus aanpassingen in te maken omdat dit dan ook terug gekoppeld moet worden naar de klanten toe (momenteel 90 klanten met 8990 subnummers!!!) en de Holding die daar écht niet blij van wordt.

En 2 kolommen aanmaken, waarbij je de structuur van curry volgt, maar deze gaat vullen met Triggers?

[Polderdijk]

Ik ga proberen om een extra kolom toe te voegen en dan overal 0-en toevoegen, en dan op deze sorteren, maar dan het origineel uitlezen en tonen...

[Woy]

Kan je niet gewoon een User Defined function maken die de char values eruit filtert en dan convert naar een float/double. Dan kan je aan de hand van deze functie gaan sorteren?

[Polderdijk]

rwb schreef op 02 October 2003 @ 14:40:
Kan je niet gewoon een User Defined function maken die de char values eruit filtert en dan convert naar een float/double. Dan kan je aan de hand van deze functie gaan sorteren?

Ik ben nu nog niet in zo'n stadium dat ik dit zomaar even kan implementeren, eerst ga ik verder met de overige dringende zaken die nog in het programma moeten komen en daarna, als ik het wat rustiger hebt ga ik een goede cursus volgen van SQL 2000 en kan ik hopelijk daarna het probleem oplossen!

Echter had ik verwacht dat ik het op een redelijk simpele manier kon oplossen net zoals ik vroeger in access hebt gedaan, maar dat viel een beetje tegen.

In ieder geval bedankt voor alle brainstorms die veroorzaakt zijn door mijn probleem!

[chem]

Wat je eigenlijk wil doen is een Natural Sort... je zou ff moeten googlen of dat mogelijk is in MSSQL.

[JaQ]

chem schreef op 02 October 2003 @ 15:18:
Wat je eigenlijk wil doen is een Natural Sort... je zou ff moeten googlen of dat mogelijk is in MSSQL.

ik kan in ieder geval melden dat dat in Oracle, PostgreSql en MySql niet mogelijk is. Ik verwacht dus niet dat dat wel mogelijk is in MSSQL. Over het algemeen los je dat op in je programmatuur die je er boven hebt liggen.

[Polderdijk]

chem schreef op 02 oktober 2003 @ 15:18:
Wat je eigenlijk wil doen is een Natural Sort... je zou ff moeten googlen of dat mogelijk is in MSSQL.

Geweldig,

Ik heb het nu zo gedaan:

SQL:

SELECT
    *
FROM
    Subnummer
WHERE
    Ordernummer = RS("Ordernummer")
ORDER BY
    RIGHT(REPLICATE('0', 10) + Subnummer, 10)

En dit is precies wat ik wilde hebben!!!

[ Voor 12% gewijzigd door Polderdijk op 02-10-2003 15:25 ]

[Verwijderd]

De oplossing zou ik zoeken in voorloopspaties. In je SQL-statement iets in de trant van (pseudocode, niet getest):
right(space(10) + veldnaam,10)

Dat zou met SQL-server gewoon moeten werken.

Edit: (oeps, laatste post niet gezien.... beetje mosterd na de maaltijd)

[ Voor 17% gewijzigd door Verwijderd op 02-10-2003 16:18 ]

[Verwijderd]

Gebruik het volgende maar eens:
[sql]
SELECT * FROM Tabelletje ORDER BY RIGHT('0000000000'+Subnummer,10)
[/sql]

Ervan uitgaande dat Subnummer dus een nvarchar oid is.




Oei, je had de oplossing al. Lezen, Typhon, lezen....

[ Voor 24% gewijzigd door Verwijderd op 02-10-2003 18:02 ]