Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 20 total
Thread Numeric sorting
Wed, Apr 19 2006 7:20 AMPermanent Link

Is there any way to have DBISAM sort text "naturally"?
That is, where these would come out in the normal order:

item1
item10
item11
item2
item20
item200
item21

/Matthew Jones/
Wed, Apr 19 2006 7:31 AMPermanent Link

"Ralf Mimoun"
Matthew Jones wrote:
> Is there any way to have DBISAM sort text "naturally"?
> That is, where these would come out in the normal order:
>
> item1
> item10
> item11
> item2
> item20
> item200
> item21

If you know that there is always an "item" at the beginning, you can use the
string functions and CAST AS INTEGER.

Ralf
Wed, Apr 19 2006 8:29 AMPermanent Link

Unfortunately I don't know that. The data is "completely random" as far as
my apps are concerned. 8-(  This is to do with user (human!) expectations.

/Matthew Jones/
Wed, Apr 19 2006 8:42 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Matthew,

<< Is there any way to have DBISAM sort text "naturally"? That is, where
these would come out in the normal order: >>

The only way is to define another integer field in the table, create an
index on it, and then put the parsed numeric in there during updates.

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Apr 19 2006 8:57 AMPermanent Link

"Robert"

"Matthew Jones" <matthew@matthewdelme-jones.delme.com> wrote in message
news:memo.20060419132727.5256J@nothanks.nothanks.co.uk...
> Unfortunately I don't know that. The data is "completely random" as far as
> my apps are concerned. 8-(  This is to do with user (human!) expectations.
>

You pretty much answered your question. Unless there is some logic that you
can apply more or less consistently, such as the item number is the ONLY
numeric portion of the string, or always the FIRST, or something like that,
you are limited to a conversion program where you present the string to the
user and have them determine what the item number is, and enter it into a
new indexed field.

Robert


Wed, Apr 19 2006 9:59 AMPermanent Link

"Ralf Mimoun"
Matthew Jones wrote:
> Unfortunately I don't know that. The data is "completely random" as
> far as my apps are concerned. 8-(  This is to do with user (human!)
> expectations.

Then a server defined function: get all digits from the end, and convert
them to an integer.

Ralf
Wed, Apr 19 2006 10:33 AMPermanent Link

"David Farrell-Garcia"
Matthew Jones wrote:

> Is there any way to have DBISAM sort text "naturally"?
> That is, where these would come out in the normal order:
>
> item1
> item10
> item11
> item2
> item20
> item200
> item21
>
> /Matthew Jones/

That is a common issue and is not a Dbisam thing.  It does the same
thing with any string sorting, AFAIK.  There are a number of solutions,
one of which we employed in a previous app was to add a sort field to
the table.  When a record was added we would covert the character field
into a numeric field using its ASCII position.  Then in the app if the
user wanted to sort on the character field we would force it to sort on
the hidden sort (numerical) field.  Another option is to pad the field
to the right which gives all records the same length so they sort as
excpected.
Wed, Apr 19 2006 10:55 AMPermanent Link

While I accept that this is something that isn't available now, this
method of sorting isn't hard to do (indeed I did it myself in 1988 in a
Windows app!) and it helps with the user experience a lot. It costs a
little time, but is a matter of the compare routine comparing text until
it finds a number, then comparing the numbers (not their text) and then
reverting to the text as usual.

Hopefully one for an ElevateDB update. I'd bet it would be copied around
smartish!

/Matthew Jones/
Wed, Apr 19 2006 10:55 AMPermanent Link

Thanks - perhaps something for the ElevateDB wishlist? 8-)

/Matthew Jones/
Wed, Apr 19 2006 11:24 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Matthew


I think you're going to struggle no matter what you do.

Adding a hidden sort order field for the numeric part won't work unless the alpha part is always identical and I bet it isn't Smiley

Writing custom functions won't work if you want to use TDBISAMTables and display in the "right" order.

Padding the integer part will work if you can determine a maximum integer size to pad to, but might get right up users noses Smiley

The best solution is split the field into two and have a compound index but that's probably not going to be acceptable to the users or the system design!

Unfortunately us idiotic humans persist in thinking of AAANNN as text + number when the computer thinks its just text.


Roy Lambert
Page 1 of 2Next Page »
Jump to Page:  1 2
Image