Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    May 2006
    Posts
    10

    Unanswered: Sorting strings in Alphanumeric order

    Hi people,

    I have a database Oracle and i have a table called RELATORIO and this table has a column named NOME. I need to retrieve all objects from table RELATORIO in order.

    Suppose I have: A 1, A 10, A 3, A 2 . I would like to retrieve the objects in the following order A 1, A 2, A 3, A 10.

    But today when a execute a SELECT plus Order By I get A 1, A 10, A 2, A 3

    How I can solve this problem? Can I use NLS-SORT? How?

    thanks

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    You'll have to tell it what you know and it doesn't - that you want to sort by the first space-separated word, then by the second word treated as a number. Use SUBSTR and INSTR to get the separate elements. For the second word, either use TO_NUMBER or LPAD.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    In other words


    Code:
    select nome
    from RELATORIO
    order by substr(none,1,instr(none,' ',1)-1),
                 to_number(trim(substr(none,instr(none,' '))));
    This code assumes that nome ALWAYS follows the pattern of an alpha string followed by a blank, followed by a number.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    May 2006
    Posts
    10

    Sorting strings in Alphanumeric orde

    The solutions suggested by my friends don't solve for generic names. I give one example, but I need the solution be generic.

    It is possible configurate a new attribute to be used with NLS_SORT?

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I don't think NLS_SORT could help you here, and I don't see how you would get a generic solution here : your numbers are inside a string, so they will be sorted as strings unless they are converted to numbers (hence WilliamR and beilstwh's proposition).

    Maybe it's a design issue : shouldn't the first letter be stored in a different column from the following number ?

    col1 col2
    A 1
    A 10
    ...

    In this case col2 would be a number and would be sorted as such, not as a string .

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  6. #6
    Join Date
    May 2006
    Posts
    10
    I have a java graphic interface and in it the users can create the object relatorio. This object has a lot of attributes among them we have "nome".
    The nome attribute can assume different alphanumeric values, because the user give the name it want. After created the objetc relatorio it is stored in a database using Interbase.

    There is another interface that allows users view the relatorios created before. I use a SQL (select + order by) to retrieve this datas. The order should consider number. I cann't break the variable name and put it in another column. This is not efficient.

  7. #7
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Quote Originally Posted by Aloizio
    There is another interface that allows users view the relatorios created before. I use a SQL (select + order by) to retrieve this datas. The order should consider number. I cann't break the variable name and put it in another column. This is not efficient.
    I don't know much about your system but having two columns as your primary key is efficient, even better if one or both are numeric.
    I'd definitely split the column.

    There's nothing you can do with NLS_SORT since the column is alphanumeric, not numeric.
    You'll have to work with beilstwh suggestion or something similar.

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Care to let us know _at least_ the rules behind this game (you know, what can and not be possible) ?

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I agree with DKG, I have been programming for 25 years and I have found that it is much more efficent to have data broken up into different columns then to have to parse a single column every time it is used. However, if the nome column is a single identifer where the component parts don't mean anything, then leave it together.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    May 2006
    Posts
    10

    Sorting strings in Alphanumeric order

    I have already solved the problem in the application level but I need to solve in the Oracle side when a execute a SELECT query.

    And I agreen with RBARAER that NLS_SORT won't be solve this problem.

    I can not create another column because I can have for example one string like ab32c12xxx3. In this case should I have six column?

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You do not understand what people have been asking you!!! If you have the following strings

    ab32c12xxx3
    A 1
    A 3
    A 10
    el/mfdo489430j

    WHAT are your rules to sort them. We can't suggest code unless you tell us your complete rules to sort any string your users can enter. We suggested fixes because you only showed us examples that started with a single letter followed by a numeric string. If it truly can be anything, what are the sorting rules???
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #12
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by Aloizio
    I have already solved the problem in the application level but I need to solve in the Oracle side when a execute a SELECT query.

    And I agree with RBARAER that NLS_SORT won't be solve this problem.

    I can not create another column because I can have for example one string like ab32c12xxx3. In this case should I have six column?
    Now you've completely lost me. "ab32c12xxx3" would be one column, wouldn't it, because it's one word? I assumed the spaces indicated word boundaries, in which case perhaps you could use REGEXP_REPLACE to left-pad each word with zeroes, but evidently there are more rules to this than I first thought.

    NLS_SORT and NLS_COMP are not limited to numeric variables, but they are designed to support international character sets, not arbitrary systems in which "ab32c12xxx3" should come after ""ab3zc9xyz?bananarama".

    How did you solve the problem at the application level?

  13. #13
    Join Date
    May 2006
    Posts
    10
    beilstwh the order should be:

    A 1
    A 3
    A 10
    ab32c12xxx3
    el/mfdo489430j

    In another case, we can have the following sort:

    1 ano
    A1
    A 1
    A 3
    A 10
    ab32c12xxx3
    el/mfdo489430j

    I wait this can answer your question.

  14. #14
    Join Date
    May 2006
    Posts
    10
    WilliamR,

    Yes "ab32c12xxx3" must be in one column. String and Numbers can be infix (MN1C) or suffix (Special Firms Team 10) or just a simple string.

    I did one study over NLS_SORT and NLS_COMP they doesn't solve the problem.

    In the application level I broke the original string into STRINGs and NUMBERs then I put in one Array. The sorting was processed considering this Array.

    I am almost going to arrive to the conclusion that the issue "I have a alphanumeric column in the database. Is it possible to do an alphanumeric sort directly by a sql command like "order by <column>" " isn't possible in the database level.

  15. #15
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by Aloizio
    "I have a alphanumeric column in the database."
    I'm sorry to say no : you have a string column, which is sorted as such - alphabetically. If you really need the sort you want to do, then I would say that the problem is that your design does not match your business rules. The only solution then is to accept it and re-design your database schema (for example, maybe you are putting things together that should be put apart : these different codings should maybe lie in different tables).

    If you are ready to think about a re-design, then you can explain more precisely what you have to do and we'll be able to help you.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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