Results 1 to 4 of 4

Thread: Order By

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Order By

    Hi

    Could someone please tell me how I can 'Order By' alphabetically (ignoring the preceding numbers)?
    Code:
    E.g., 12345_ABC
    17890_DEF
    23579_BAZ
    so that I get

    12345_ABC
    23579_BAZ
    17890_DEF



    Regards
    Shajju
    Last edited by shajju; 08-31-14 at 08:33.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ORDER BY <an expression to remove leading numbers>

    The key issue might be what format variations in the column?

    e.g.
    suppose one of simplest case that the format was fixed with "nnnnn_ccc" where n is a number and c is an alphabet.
    Then, <an expression to remove leading numbers> might be
    SUBSTR(a_column , -3) /* result: 'ccc' */
    or
    SUBSTR(a_column , -4) /* result: '_ccc' */

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yet another option, using regular expressions:
    Code:
    order by regexp_substr(a_column, '[[:alpha:]]+$');

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    Thanks guys. Much appreciated.

Posting Permissions

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