> 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/

"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

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/

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

"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

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

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

"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

> 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/

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