RE: Sorting "character" numbers

[Buck Calabro <buck.calabro@xxxxxxxxxxxxxxxxx>]

Brad Stone wrote:

>We have a DB that contains a character field 
>which holds an invoice number. Because it 
>can be used for many of our companies, we 
>had to make it character because some of 
>our companies use character invoice numbers.
>
>Anyhow, the plants that use numeric numbers 
>get the number stripped of leading zeros 
>(this is done automatically by the replication tool 
>we use). So, it makes the number sort all funky.  
>(ie 900 is greater than 1101.)
>
>Is there any way, without having to leave on 
>leading zeros, to get them to sort correctly?  
>Probably not, but with the minds on the list I 
>wouldn't be surprised.  ;)

If you can't allow right justify, try this:
Create a new (sort) table containing the "real" key and a sort field.  Use a
trigger on the existing table to populate the new table with the "real" key
and the sort field.  The sort field will be left justified for alphameric
invoices and right justified for numeric ones.  When you need to actually
sort the existing file, join it to the sort file and sort via the sort field
(which is a "de-funkified" invoice number.)  

I think it's ugly but I can't see how an alternate collating sequence will
help here.
Good luck!

Buck Calabro
Aptis; Albany, NY
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---