|
midrange.com
Off the top of my head, a CASE statement (to separate alpha and numeric)
combined with a SUBSTR using LENGTH and TRIM for the numerics would work.
For example, to right-justify with zero-pad into a ten-digit field:
FIELD = SUBSTR('0000000000', 1, 10-LEN(TRIM(FIELD))) + TRIM(FIELD)
Note that this will fail for fields of length 10 or more.
Joe
> -----Original Message-----
> From: Hatzenbeler, Tim
>
> I have a field, it's 20 char long, left justified..
>
> It has things like:
> Accounting
> Education
> 110
> 1101
> 220
> 2234
>
>
> The problems is sorting... Because it's not a numeric field, and there are
> are no '0's infront of the number to make them the same length or right
> justified, their not sorted correctly...
>
> Is there a way in a sql statement, to check the field to test if it's
> numeric, and if its, a valid number, (cast) it as an integer, and sort it
> that way?
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
