Results 1 to 7 of 7

Thread: order by

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: order by

    Hi there.

    I have a VARCHAR(2) column containing 10 to 15 character values with the 4th and 5th characters being numbers.
    Can you help me order that column by those numbers in ascending order? like 1,2,3,10,11?

    E.g.,
    Code:
    ABC1BNM
    ABC2BNM
    ABC3BNM
    ....
    ABC10BNM
    ABC11BNM
    I've tried LPAD unsuccesfully. If it was just numbers I'd use ABS or CAST but the string contains letters and numbers.

    Regards
    Shajju

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    select *
    from your_table
    order by to_number(regexp_replace(some_col, '[^0-9]*', ''))
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Thanks so much Shammat. I forgot to mention, some strings have random numbers at the end which after running your query, are not allowing that particular entry to be sorted numerically. The rest of the rows were ordered just fine.

    Code:
    E.g.,
    
    ABC1BNM
    ABC2BNM
    ABC3BNM
    ........
    ABC10BNM
    ABC11BNM
    ABC7BNM2
    QWE8RTY2
    ABC12BNM

    Regards
    Shajju

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Try this:
    Code:
    order by to_number(regexp_replace(some_col, '([^0-9]{3})([0-9]{1,2}).*', '\2'))
    This takes up to two numbers after the first three non-number characters
    Last edited by shammat; 11-12-12 at 07:02.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Yes it does.

  6. #6
    Join Date
    Aug 2008
    Posts
    464

    Appreciated

    Thanks a lot. It works a treat.

    The statement looks quite complicated. Just out of curiosity, is this the easiest way to do it?

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    Can I ask one more thing?

    That is the first part of my query. Then I'm unioning it with
    Code:
    select 
    '--',
    to_char(sysdate-1),
    to_char(sysdate-2),
    to_char(sysdate-3),
    to_char(sysdate-4),
    to_char(sysdate-5),
    to_char(sysdate-6),
    to_char(sysdate-7),
    to_char(sysdate-8),
    to_char(sysdate-9),
    to_char(sysdate-10),
    to_char(sysdate-11),
    to_char(sysdate-12),
    to_char(sysdate-13),
    to_char(sysdate-14)
    from 
    dual
    So that the top line shows the dates.

    But as '--' is hard-coded in the above query, using your order by statement returns 'Invalid number'. I've tried doing
    Code:
    to_number('--')
    but as the column with which to_number('--') is to be unioned with is of data type 'to_char', I get:

    Code:
    ORA-01790: expression must have same datatype as corresponding expression
    I think I'm cornered.

Posting Permissions

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