Sorting on charactor field

Yahoo Archive
1
Thank you so much,
SUBSTRING(field,1,1) -> sort 1
NUMERIC(SUBSTRING(field,2,3)) -> sort 2

worked perfect.


--- In vantage@yahoogroups.com, "Aaron Hoyt" <aaron.hoyt@v...> wrote:
> First sort by the first Character and then by the rest of the field.
> Maybe Start with...
>

>
> And then use something like this calculated field for the numeric
section...
>
> FILL(0,(3 - Length(SUBSTRING(field,2,3)))) & SUBSTRING(field,2,3).
>
> If not,
> You might be able to use something like...
>
>
>
> I have not tested it, but this should lead you down the path.
>
> Good luck,
> Aaron Hoyt
> Vantage Plastics
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On
Behalf
> Of sharma_nalini
> Sent: Friday, April 22, 2005 5:56 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Sorting on charactor field
>
>
>
>
> Hi ,
>
> We have our binnum enetered as A1-A100 then B1-100 and so on...
> Right now sort recirds are A1,A10,A100 then A2, A21...
>
> How can we sort them correctly like A1 then A2.....B1 then B2...
>
>
>
>
>
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You
must have
> already linked your email address to a yahoo id to enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report
Builder and
> Crystal Reports and other 'goodies', please goto:
> http://groups.yahoo.com/group/vantage/files/.
> (2) To search through old msg's goto:
> http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto:
> http://groups.yahoo.com/group/vantage/links
> Yahoo! Groups Links
2
Hi ,

We have our binnum enetered as A1-A100 then B1-100 and so on...
Right now sort recirds are A1,A10,A100 then A2, A21...

How can we sort them correctly like A1 then A2.....B1 then B2...
3
It is a common problem (not just Vantage) when sorting on mixed
alpha and numeric characters. We know how they should be sorted,
but the computer doesn't. You will need to create new bin numbers
with leading zeros, so you bins run A001 - A100. Transfer from A1
to A001, then delete A1. Not pretty (especially if you go to Z100),
but it should clear up the problem.

Michael Randolph
Purchasing Manager
American Magnetics, Inc.

--- In vantage@yahoogroups.com, "sharma_nalini" <sharma_nalini@y...>
wrote:
>
> Hi ,
>
> We have our binnum enetered as A1-A100 then B1-100 and so on...
> Right now sort recirds are A1,A10,A100 then A2, A21...
>
> How can we sort them correctly like A1 then A2.....B1 then B2...
4
First sort by the first Character and then by the rest of the field.
Maybe Start with...

SUBSTRING(field,1,1).

And then use something like this calculated field for the numeric section...

FILL(0,(3 - Length(SUBSTRING(field,2,3)))) & SUBSTRING(field,2,3).

If not,
You might be able to use something like...

NUMERIC(SUBSTRING(field,2,3)).

I have not tested it, but this should lead you down the path.

Good luck,
Aaron Hoyt
Vantage Plastics

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On Behalf
Of sharma_nalini
Sent: Friday, April 22, 2005 5:56 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Sorting on charactor field




Hi ,

We have our binnum enetered as A1-A100 then B1-100 and so on...
Right now sort recirds are A1,A10,A100 then A2, A21...

How can we sort them correctly like A1 then A2.....B1 then B2...






Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Links