Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 11 to 20 of 20 total |
Numeric sorting |
Wed, Apr 19 2006 12:06 PM | Permanent Link |
> Another option is to pad the field
> to the right which gives all records the same length so they sort as > excpected. The data is imported from any normal data file, but I guess I could pre- process them for a sort field and extend numeric portions to some number of characters... /Matthew Jones/ | |
Wed, Apr 19 2006 1:34 PM | Permanent Link |
"Robert" | "Matthew Jones" <matthew@matthewdelme-jones.delme.com> wrote in message news:memo.20060419155416.5256L@nothanks.nothanks.co.uk... > While I accept that this is something that isn't available now, this > method of sorting isn't hard to do (indeed I did it myself in 1988 in a > Windows app!) and it helps with the user experience a lot. It costs a > little time, but is a matter of the compare routine comparing text until > it finds a number, then comparing the numbers (not their text) and then > reverting to the text as usual. > The issue is whether you can find (unambiguosly) the numeric portion, or not. If you can (and you seem to imply that in your case you do) then the rest is trivial. Create a new field, set it as an index, and in BeforePost you extract from the user string whatever portions you need and update the new index field. The user does not even know such field exists, but the table is magically indexed with the correct value. But it the string can be something like "ingest 5 spoons of medicine 123" and you end up with the patient taking 123 spoons of medication number 5, you are in deep doodoo. Robert |
Wed, Apr 19 2006 3:18 PM | Permanent Link |
Finding the numbers is easy - there are ten ANSI characters starting with
'0' and they go up to '9'. This works best in a string comparison routine of course - when comparing two strings the numbers only get looked at if they are the same up to that point. Heck, the code is easy: // Routine to compare strings with numbers in. It results in Fred10 being greater than // Fred5, to produce a more natural sort. int gl_strcmpx(const char GLOBAL *lpFirst, const char GLOBAL *lpSecond) { int iDiff, index; char caFirstNum[50], caSecondNum[50]; while(*lpFirst) { if(isdigit(*lpFirst) && isdigit(*lpSecond)) { index = 0; while(isdigit(*lpFirst) && index < (50 - 1)) caFirstNum[index++] = *(lpFirst++); caFirstNum[index] = '\0'; index = 0; while(isdigit(*lpSecond) && index < (50 - 1)) caSecondNum[index++] = *(lpSecond++); caSecondNum[index] = '\0'; iDiff = atoi(caFirstNum) - atoi(caSecondNum); if(iDiff != 0) return(iDiff); // else carry on, already from after the numbers } if(*lpFirst == *lpSecond || (gl_toupper(*lpFirst) == gl_toupper(*lpSecond))) { lpFirst++; lpSecond++; } else { break; } } return(gl_toupper(*lpFirst) - gl_toupper(*lpSecond)); } /Matthew Jones/ | |
Wed, Apr 19 2006 11:27 PM | Permanent Link |
"Adam H." | Hi Matthew,
> Is there any way to have DBISAM sort text "naturally"? > That is, where these would come out in the normal order: > > item1 > item10 > item11 There is a couple way to do this, using DBISam, which I have implemented. The first way (already suggested) is to have another integer field, and populate that field with the remaining numbers, however recently I came up with a different idea. If you are using DBISamQueries, why not create a custom function? This is what I have done: If you don't already have a TDBISamEngine component within your app, dump one on your form. Add the following code to the two methods (Onstartup and oncustomfunction) procedure TMainForm.DBEStartup(Sender: TObject); begin // Create Custom Functions with mainform.DBE.Functions.CreateFunction(ftfloat , 'MakeSortOrder').Params do CreateFunctionParam(ftString); end; procedure TMainForm.DBECustomFunction(Sender: TObject; const FunctionName: String; FunctionParams: TDBISAMParams; var Result: Variant); begin if (AnsiCompareText(FunctionName,'MakeSortOrder')=0) then try Result := MakeSortOrderFloat(FunctionParams[0].asstring); except result := 0; end; end; Add the following function to your unit (it may be a bit sloppy, but it works. If you have a better method of performing the same function, I'd love to see it, as I just threw this one together : function MakeSortOrderFloat(Val : String): double; VAR tmp: string; lengthtoget: Integer; floatflag: boolean; flag: Integer; I : Integer; s : String; begin if right(val, 1) = '.' then val := copy(val, 1, length(val)-1); s := ''; flag := 0; try for I := length(val) downto 0 do if i > 0 then if not (val[i] in ['0'..'9']) then begin floatflag := false; if val[i] = '.' then if pos('.', copy(val, i+1, 100))=0 then if i > 1 then if val[i-1] in ['0'..'9'] then begin floatflag := true; end; if not floatflag then begin if flag = 2 then break; flag := 1; lengthtoget := length(s); if pos('.', s) > 0 then begin tmp := copy(s, 1, pos('.',s)-1); lengthtoget := length(tmp); end; s := copy('000000',1, 6-lengthtoget)+s end else begin s := val[i] + s; if flag = 1 then flag := 2; end; end else s := val[i]+s; if s <> '' then result := strtofloat(s) else result := 0; except result := 0; end; end; Now, all you need to do, is implement a query with the following SQL: select Myfield, MakeSortOrder(MyField) from mytable order by 2 This will order it by the MakeSortOrder field. Hope this helps... Adam. -- There is a principle which is a bar against all information, which cannot fail to keep a man in everlasting ignorance-- that principle is contempt prior to investigation." - Herbert Spencer, British philosopher. There is a second principle which has the same results as the 1st - that principle is not caring about being ignorant" - Unknown |
Thu, Apr 20 2006 12:20 AM | Permanent Link |
"GregF" | "Matthew Jones" <matthew@matthewdelme-jones.delme.com> wrote in message news:memo.20060419121838.5256H@nothanks.nothanks.co.uk... > Is there any way to have DBISAM sort text "naturally"? > That is, where these would come out in the normal order: > > item1 > item10 > item11 > item2 > item20 > item200 > item21 > > /Matthew Jones/ As an old Codd/Date guy from years of Data Analysis experience I will categorically tell you that what you are describing is two separate attributes thus it should be two separate fields It is in fact contrary to Codd/Date SQL design that a field have embedded meaning I for one have never seen this as a DBMS solution but rather as a data design issue. Sure you may be converting legacy systems but that should be seen as an opportunity to repair bad table design in the first place. gregF |
Thu, Apr 20 2006 5:45 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Matthew,
<< Thanks - perhaps something for the ElevateDB wishlist? 8-) >> Already there - you can define virtual, generated columns that are basically expressions that can reference any other column. And they can be indexed. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Apr 20 2006 6:00 AM | Permanent Link |
"Surjanto" | Tim,
> Already there - you can define virtual, generated columns that are > basically expressions that can reference any other column. And they can > be indexed. > Are you talking about TDataset descendant or the database itself, and applies to memory or physical table ? Regards, Surjanto |
Thu, Apr 20 2006 7:35 AM | Permanent Link |
"Robert" | "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message news:0A7B3349-E32C-496F-B492-C54BC741FABF@news.elevatesoft.com... > > Already there - you can define virtual, generated columns that are > basically expressions that can reference any other column. And they can > be indexed. > Excellent. Robert |
Thu, Apr 20 2006 10:45 AM | Permanent Link |
> I will categorically tell you that what you are describing is two
> separate attributes thus it should be two separate fields 8-) I can categorically tell you that isn't what I'm describing! I'm describing the names that our customers give to their analysis units sometimes. Most often they'll be things like "London" and "Paris", but sometimes they just use numbers in the text. I would just be nice to show it in a human sorted format. /Matthew Jones/ | |
Thu, Apr 20 2006 1:38 PM | Permanent Link |
"Jerry Clancy" | Amen to this (as I look at my copy of Date's "An Introduction to Database
Systems"). In our Congressional product I have to deal with all sorts of references to bills, eg, H.CON.RES.24 H.R.2043 J.CON.RES.87 s.56 hr90 hr 90 etc. As Greg notes, you have to deal with it and make the appropriate database design decisions. In our case we have functions that parse and reformat the references into separate, uniform key fields which collate properly. We know that the number part can never exceed 5 digits. We have functions which pad left with blanks or 0's, as required. You do what you gotta do. Jerry "GregF" <footyfacts@yahoo.com> wrote in message news:7BB350F0-37A7-4156-956E-8FCEF5E5C8A7@news.elevatesoft.com... | | "Matthew Jones" <matthew@matthewdelme-jones.delme.com> wrote in message news:memo.20060419121838.5256H@nothanks.nothanks.co.uk... | > Is there any way to have DBISAM sort text "naturally"? | > That is, where these would come out in the normal order: | > | > item1 | > item10 | > item11 | > item2 | > item20 | > item200 | > item21 | > | > /Matthew Jones/ | | As an old Codd/Date guy from years of Data Analysis experience | I will categorically tell you that what you are describing is two | separate attributes thus it should be two separate fields | | It is in fact contrary to Codd/Date SQL design that a field | have embedded meaning | | I for one have never seen this as a DBMS solution | but rather as a data design issue. | | Sure you may be converting legacy systems | but that should be seen as an opportunity | to repair bad table design in the first place. | | gregF |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |