Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: dan delpuerto
  
 
 
 dan delpuerto
 To: Andy Smith
  Where is Andy Smith?
 Bloomfield
 New Jersey - United States
 Andy Smith
 Tags
Subject: RE: Indexing in Character field
Thread ID: 57009 Message ID: 79267 # Views: 1 # Ratings: 0
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Friday, October 14, 2005 4:06:32 PM         
   


Please try this and let me know if this works


select iif(isAlpha(number),number + space(5), padl(allt(number),5," "));
as xx, number ;
from test;
order by xx


Note 1. This creates xx column for indexing.
2. Numbers will be ordered not as alphabets are but ("10" > "2")
3. Alphabets will be in alphabetical order ("B" > "ABCDEFG") or
you may use the same proc as for numbers
4. Change length (5) to max length of items

Good luck


> > > Hi All,
> > >
> > > In a character field numeric and Character values are stored.
> > >
> > > The following query
> > >
> > >      Select number from test order by number 
> > >      

> > >
> > > gives Output like this
> > > 1
> > > 10
> > > 2
> > > 3
> > > A etc...
> > >
> > > How to get the output like this
> > > 1
> > > 2
> > > 3
> > > 10
> > > 11
> > > A etc., More over If I use PADL A is coming after 3.
> > >
> > > Thanks
> > >
> > > R.HARI HARAN
> >
> > SELECT VAL(number) as nOrder, ;
> > number ;
> > FROM test ;
> > ORDER BY 1
> >
> > Cheers,
> > Jamie
> > jamie.osborn@foxite.com
>
> Two things: the sort order should have numbers first in numerical order, letters last in alphabetical order, right? But VAL(letters) will be zero, so letters will come first, and with only one ORDER, they'll be unalphabetized. You need to do this:
>
>
> CALCULATE max(val(number)) FROM test TO nMax	&& Or just "nMax = 9999" if you know an upper bound
> SELECT MOD(nMax + val(number)), nMax + 1) as nOrder, test.* FROM test ORDER BY nOrder, number
> 


ENTIRE THREAD

Indexing in Character field Posted by HARI HARAN RAMACHANDRAN @ 12/3/2004 6:24:52 AM
RE: Indexing in Character field Posted by Jamie Osborn @ 12/3/2004 4:09:05 PM
RE: Indexing in Character field Posted by Andy Smith @ 12/3/2004 8:09:25 PM
RE: Indexing in Character field Posted by dan delpuerto @ 10/14/2005 4:06:32 PM