We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

HELP.. Alpha Numeric sorting routines

Medium Priority
304 Views
Last Modified: 2013-11-24
If anyone has an Alphanumeric sorting routine that can be used for DBase tables would it be possible to post it up. This would be of great benefit to me and am willing to offer points for it. Make it 200 say.
                             Muchly appreciated WESTY  
Comment
Watch Question

What kind of sorting are you looking for?  SQL?  Table indeces? General sorting routines in code (not only for DBase)?

JB

Author

Commented:
I am looking mainly for a sort routine for Table indeces. But could also use one for normal sort routines in code that is also.
Don't know if you can help but if you can then thanks in Advance.
Hi

I'm still not 100% sure what you are looking for, but there are several ways to sort DBase data:

1) You can put an index on the field you want to sort by.  Do this in Database Desktop; open your table, choose Restructure on the Table menu, & then choose "Define" for the Indeces.

2) You can enter data into the table in any order, and extract it in the order you want.  This is done with a query.  E.g.  If you have a table called "Cust" with a field "CustName", you can put a TQuery component on a form, and in the SQL property type:

SELECT * FROM Cust ORDER BY CustName

(You will obviously have to give the TQuery a DatabaseName, and you may want to link it to a TDBGrid via a TDataSource.)

These are 2 common ways of sorting database data alphanumerically.

JB

Author

Commented:
JB,
   The heart of my problem is this...I am adding records to a table that has several different indeces. Now with Aplhanumeric
fields or Character fields the data will not be ordered correctly. ie Numeric fields list 1,2,3,4,5,6,...etc
              Character fields list 1,10,11,12,13,14,15,16,17,
                                    18,19,2,20 or something like this.
Now i need to show the data in the correct order at all times so
and so i think doing it dynamically is the best way. It has to be an Alphanumeric field too as our clients have requested it. I guess i am wondering if it is possible to display an active table
that is indexed using an Alphanumeric field in the correct order all the time not just sort it once and thats it. Does that give you some light on the direction i am looking.
                                              WESTY    

Hi

I see what you're aiming for now, and I'm afraid that with the limited SQL in DBase & Paradox you may struggle to get it right.  I once had a similar situation (i.e. a field with letters & numbers, and the value "2" must come before "11") and this is how I solved the problem.  (It is a little clumsy, and maybe there's a better way, but it worked for me...)

I created a dummy field, and in my table's OnBeforePost event I created the value for my dummy field.  E.g. My main field is called ALPHAMAIN and my dummy field is ALPHADUMMY.

In the OnBeforePost event I check the value of ALPHAMAIN.  If it is a number I insert zeroes in front to make its length 12 characters.  Thus 2 becomes 000000000002 and 12345 becomes 000000012345.  If it is not a number, I leave it.  This new value goes into ALPHADUMMY, and I have an index on ALPHADUMMY.

Here is the code for the above:

function IsNumeric(s: string): Boolean;
begin
    try
        StrToInt(s);
        Result := True;
    except
        Result := False;
    end;
end;

function Pad(ANum, NumLeadingZero: Integer): string;
begin
    Result := IntToStr(ANum);
    while (Length(Result) < NumLeadingZero) do
            Result := '0' + Result;
end;

procedure TForm1.Table1BeforePost(DataSet: TDataSet);
begin
    if (IsNumeric(Table1.FieldByName('AlphaMain').AsString)) then
        Table1.FieldByName('AlphaDummy').AsString := Pad(StrToInt(Table1.FieldByName('AlphaMain').AsString), 12)
    else
        Table1.FieldByName('AlphaDummy').AsString := Table1.FieldByName('AlphaMain').AsString;
end;

JB

Author

Commented:
OK JB thats good direction and I thank you muchly. Hey you might want to lock the quetion so I can give you the points.
                                                Cheers Westy
See comments for answer...
JB

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks again JB
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.