PDA

View Full Version : Indexing character field


ABC123

M_Mowat
11-12-2002, 05:08 PM
I have a character field called "location". The field will read "Box 1, Box 2, Box 3, and so on to approx 60. When the database is indexed on this field, for example to print a report or perform a query or range, they are indexed as follows; Box 1, Box 10, Box 11,... up to Box 19, then Box 2,
Box 20, Box 21, Box 22,... Is there a way to write a script to force the database to be indexed the way I need it to be?
Box 1, Box 2, Box 3...
Thanks in advance

Allen Klimeck
11-12-2002, 05:28 PM
Change (Box 1 through Box 9) to (Box 01 through Box 09) and it will index correctly.

Melvin Davidson
11-14-2002, 05:09 AM
There are two solutions.

Unless the field always starts with "BOX", I'm guessing it
does not, you should break it up into two fields:
eg: Box_Alpha & Box_Numeric.
Then your index would be BOX_ALPHA + STR(VAL(BOX_NUMERIC))

otherwise
LEFT(CHAR_BOX, 3)+ STR(VAL(RIGHT(CHAR_BOX, 6)),6)

Also works.

regards,
Melvin Davidson