2 Replies Latest reply: May 6, 2011 9:10 PM by user10664276 RSS

    how to display only number from bank account name

    user10664276
      Hi ,
      i would like to dispaly account number from bank account name if bank account num is null.

      for example

      select
      nvl(bank_account_num,substr(bank_account_name,6)
      from ap_checks_all

      HDFC_0000000000000
      SBI_0000000000000

      i need to display 00000000000 from both the bank name.

      Thanks
        • 1. Re: how to display only number from bank account name
          Frank Kulash
          user10664276 wrote:
          Hi ,
          i would like to dispaly account number from bank account name if bank account num is null.

          for example

          select
          nvl(bank_account_num,substr(bank_account_name,6)
          from ap_checks_all
          You have the right idea. You have 2 left "(" characters, but only 1 right ")". I think you meant:
          SELECT  NVL ( bank_account_num
                   , SUBSTR (bank_account_name, 6)
                   )                     AS account_num
          FROM    ap_checks_all
          HDFC_0000000000000
          SBI_0000000000000

          i need to display 00000000000 from both the bank name.
          SUBSTR (bank_account_name, 6) will be from the 6th character to the end of the string. If you want everything after the "_", wherever that happens to be, then use:
          SELECT  NVL ( bank_account_num
                   , SUBSTR ( bank_account_name
                               , 1 + INSTR (bank_account_name, '_') 
                         )
                   )                     AS account_num
          FROM    ap_checks_all
           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using.