The problem is not due to "character sort ordering" which is beautifully implemented in Sybase, and ANSI standard. Ie, it works correctly for character data, according to the minds that define standards, which are greater than ours.
It would appear that you have an unnormalised column (field). From your example of data, you do not have a single "sequence number" column, you actually have a string (alphanumeric) which, if it were subject to an ordinary normalisation exercise, would be identified as made up of two parts, a number and an alphabetic division within that number. Another way to state it is, the problem is in your data, and you need to correct it at that point. The proper approach is to normalise the raw data field into its two separate columns, and to define the index as the concatenation of the two columns (a compund key):
create table my_table (
major_key int not null, -- contains the "1" through "999"
minor_key char(1) not null, -- contains the "a" throgh "z"
primary key (major_key, minor_key)
This would assist the treatment/display of the table in every instance, including the simple:
to get want you want. If you absolutely need to treat the display of the data as one "field" (which it is not), you can use:
select convert(char(2),major_key) + minor_key
This way you will be storing the data correctly and massaging the treatment/display, which is superior to storing the data incorrectly (unnormalised).
There are other soutions but they are inferior, as you would be addressing symptoms of the problem, rather than the problem itself, and therefore end up doing more work overall (eg. all the code segments that use the data). Eg. Force the literal column postion for the data. Load space+"3a" for "3a"; load "10b" for "10b", etc. Very ugly.
Normalisation is essential for storage of data in a relational database, otherwise it cannot be accurately called a 'Database', it is just data or chicken soup.
Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
Anything worth doing is worth doing Right The First Time
Spend your money on standards-compliant development or spend 10 times more fixing it