8 Replies Latest reply: Jan 5, 2013 1:00 AM by Purvesh K RSS

    Handling numeric expressions

    808880
      All,

      My database version 9i,10g

      I'm trying to select following query in oracle 9i and 10g. But I'm unable to handle numeric expression. Can you help me on this ?

      select username from audit where username like 'A%';

      Anuruk
      A762473
      A462356

      But I want to retrive last two rows A762473,A462356 (starting with alphabetic A and numeric ).

      Thanks.
        • 1. Re: Handling numeric expressions
          Chanchal Wankhade
          Hi,

          try below query.
          SQL> insert into s values('Anuruk')
            2  ;
          
          1 row created.
          
          SQL>
          SQL> insert into s values('A462356');
          
          1 row created.
          
          SQL> insert into s values('A762473');
          
          1 row created.
          
          SQL> commit;
          
          SQL>
          SQL> select * from s where name like ('A%6%');
          
          NAME
          ----------
          A462356
          A762473
          • 2. Re: Handling numeric expressions
            808880
            Thanks a lot for your help. Numeric values will vary frequently dynamically like A752473, A90050 and they will generate random number.It will not helpful " like ('A%6%'); " expression.

            Values will be A123456 (but numeric values will not exceed six digits) . Can we use any regular expression or like that ?

            Thanks
            • 3. Re: Handling numeric expressions
              JohnWatson
              805877 wrote:
              Thanks a lot for your help. Numeric values will vary frequently dynamically like A752473, A90050 and they will generate random number.It will not helpful " like ('A%6%'); " expression.

              Values will be A123456 (but numeric values will not exceed six digits) . Can we use any regular expression or like that ?

              Thanks
              You don't have regular expressions in 9.x. So use smoething like
              where table_name between 'A0%' and 'A9%';
              • 4. Re: Handling numeric expressions
                Mark Malakanov (user11181920)
                you can use a simple PL/SQL function like

                create or replace function is_number(s in varchar2) return char is
                n number;
                begin
                n := to_number(s);
                return 'Y';
                exception when others then return 'N';
                end;
                • 5. Re: Handling numeric expressions
                  User286067
                  should work in 10g,
                  select a from (select 'Anuruk' as a from dual union all select 'A762473' as a from dual union all select 'A462356' as a from dual)
                  where regexp_like(a, 'A\d+')
                  /
                  • 6. Re: Handling numeric expressions
                    Purvesh K
                    805877 wrote:
                    My database version 9i,10g

                    I'm trying to select following query in oracle 9i and 10g. But I'm unable to handle numeric expression. Can you help me on this ?

                    select username from audit where username like 'A%';

                    Anuruk
                    A762473
                    A462356

                    But I want to retrive last two rows A762473,A462356 (starting with alphabetic A and numeric ).
                    Having said 9i, Regular expressions are out of scope.

                    Here is one way of achieving
                    with data as
                    (
                      select 'Anuruk' col from dual union all
                      select 'A473733' from dual union all
                      select 'A4422290' from dual union all
                      select 'B448339' from dual
                    )
                    select col
                      from data
                     where col like 'A%' and instr(translate(col, 'A1234567890', 'A~~~~~~~~~~'), '~') > 0;
                    
                    COL    
                    --------
                    A473733  
                    A4422290 
                    Logic is to translate every digit to a Special character (I chose Tilde[~], but you may choose any other too, depending on your data), and then search for the special character in the Translated string. If you find the translated character, that means you have 'A' followed by Digits (at least one).

                    If you are migrating to 10g, then regular expressions would prove handy.
                    • 7. Re: Handling numeric expressions
                      User286067
                      Purvesh K wrote:

                      Having said 9i, Regular expressions are out of scope.
                      Thanks, if i re-read original post, it said 9i,10g, which means at-least 2, hence i prefixed my answer with 10g .. fir 9i translate/replace combination would do the trick

                      Edited by: rjamya on Jan 4, 2013 3:41 PM
                      • 8. Re: Handling numeric expressions
                        Purvesh K
                        rjamya wrote:
                        Purvesh K wrote:

                        Having said 9i, Regular expressions are out of scope.
                        Thanks, if i re-read original post, it said 9i,10g, which means at-least 2, hence i prefixed my answer with 10g .. fir 9i translate/replace combination would do the trick

                        Edited by: rjamya on Jan 4, 2013 3:41 PM
                        No offence. :)

                        When two versions are provided, I target the lower version and hence made the statement.