sorting character field

Dear All,

I have this, which custID is a character field.
custID customer
----------------------
1000 ABC
2000 DBF
10000 EFG

I would like to sort in sequence 1000 -> 2000 -> 10000

I have this sql statement,
select custID ... order by custID
it gives out the result -> 1000 -> 10003 -> 2000.

What I think is covert the custID into a type integer before a sort is do
(not good method).
I would like to know if any better method.
Many thanks.

Regards,
Wandy Tang

Gregory
Thu Feb 05 02:22:13 CST 2004

Wandy,

will this do ?

select padl(CustId, 10, '0') as Id1, ;
Customer ;
from TableName ;
order by 1

Gregory
____________-
"Wandy Tang" <anonymous@discussions.microsoft.com.invalid> wrote in message
news:uNT8FU76DHA.2412@TK2MSFTNGP09.phx.gbl...
> Dear All,
>
> I have this, which custID is a character field.
> custID customer
> ----------------------
> 1000 ABC
> 2000 DBF
> 10000 EFG
>
> I would like to sort in sequence 1000 -> 2000 -> 10000
>
> I have this sql statement,
> select custID ... order by custID
> it gives out the result -> 1000 -> 10003 -> 2000.
>
> What I think is covert the custID into a type integer before a sort is do
> (not good method).
> I would like to know if any better method.
> Many thanks.
>
> Regards,
> Wandy Tang
>
>

Eric
Thu Feb 05 02:23:02 CST 2004

Hello, Wandy!

See if this is an option for you

SELECT yourfields, ;
PADL(ALLTRIM(cfield), 5, "0") AS sortfield ;
FROM yourtable ;
ORDER BY sortfield ;
INTO whatever

--
Eric den Doop
www.foxite.com - The Home Of The Visual FoxPro Experts - Powered By VFP8

Marc
Tue Feb 10 08:04:28 CST 2004

Wandy,

You are correct. The selected records appear in order for a text field which
sorts by first character, second character, etc.

You could select like so:

SELECT Custid, VAL(Custid) AS iSort..... FROM .... ORDER BY iSort

OR

SELECT PADL(ALLT(CustID), 6, '0') AS Custid ..... FROM... ORDER BY CustID

Either should work

HTH.

Marc



"Wandy Tang" <anonymous@discussions.microsoft.com.invalid> wrote in message
news:uNT8FU76DHA.2412@TK2MSFTNGP09.phx.gbl...
> Dear All,
>
> I have this, which custID is a character field.
> custID customer
> ----------------------
> 1000 ABC
> 2000 DBF
> 10000 EFG
>
> I would like to sort in sequence 1000 -> 2000 -> 10000
>
> I have this sql statement,
> select custID ... order by custID
> it gives out the result -> 1000 -> 10003 -> 2000.
>
> What I think is covert the custID into a type integer before a sort is do
> (not good method).
> I would like to know if any better method.
> Many thanks.
>
> Regards,
> Wandy Tang
>
>