Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    23

    Unanswered: Sorting of alphanumeric

    I have a character field in the table of size 3 which is used to store some sequence numbers. The numbers could be as follows

    1,2,3,3a,3b,4,...10,11,...

    due to charcter sorting the ordering I get is something while selecting from the table is as follows

    1
    10
    11
    2
    3
    3a
    3b
    4
    ..
    ..

    Could you please suggest a method so that I could sort the above as below
    1
    2
    3
    3a
    3b
    4
    ..
    ..
    10
    11
    ..
    ..

  2. #2
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    Unnormalised Data

    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:

    select major_key,
    minor_key
    from my_table


    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
    from my_table


    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.
    Derek Asirvadem
    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

  3. #3
    Join Date
    Feb 2005
    Location
    Omaha, NE
    Posts
    83
    No comment on the lecture. Here is a possible approach (untested, untried):

    select col1
    from table
    order by convert(int,substring(col1,1,patindex("^[0-9]",col1)-1))
    ,substring(col1,patindex("^[0-9]"),255))

    Don't know if I have the arguments to the functions right, but you probably get the idea.

  4. #4
    Join Date
    Feb 2005
    Location
    Omaha, NE
    Posts
    83
    After getting to test it, here is a corrected version:

    select col1
    from table
    order by
    convert(int,substring(col1,1,isnull(nullif(patinde x("%[^0-9]%",col1)-1,-1),255)))

    ,substring(col1,isnull(nullif(patindex("%[^0-9]%",col1)-1,-1),1),255)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •