This discussion is archived
8 Replies Latest reply: Jan 4, 2013 11:00 PM by Purvesh K RSS

Handling numeric expressions

808880 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points