This discussion is archived
3 Replies Latest reply: Jan 29, 2013 11:34 AM by Frank Kulash RSS

question about regexp_substr

marceloT Newbie
Currently Being Moderated
Hi, i have the following query

select regexp_substr('acgctgcactgca', 'acg(.*)gca',1,1,'i',1) from dual;

and it returns

ctgcact (from the whole string "acg ctgcact gca")

It takes the whole string, however thers another ocurrence of the pattern, acg ct gca ct gca

what if i want to get the "ct" that is between the first "acg" and the first "gca"????

any idea why it works this way or hot to get what i want?
thanks
  • 1. Re: question about regexp_substr
    odie_63 Guru
    Currently Being Moderated
    Use the non-greedy version of the quantifier :
    SQL> select regexp_substr('acgctgcactgca', 'acg(.*?)gca',1,1,'i',1) from dual;
     
    REGEXP_SUBSTR('ACGCTGCACTGCA',
    ------------------------------
    ct
     
    Are you working with DNA sequences or is it just an example?

    Edited by: odie_63 on 29 janv. 2013 18:54
  • 2. Re: question about regexp_substr
    marceloT Newbie
    Currently Being Moderated
    thanks odi, its just an example, i'm not working with DNA

    What if i want to get all possible substrings? i mean to get ct and ctgcact???
  • 3. Re: question about regexp_substr
    Frank Kulash Guru
    Currently Being Moderated
    Hi
    marceloT wrote:
    thanks odi, its just an example, i'm not working with DNA

    What if i want to get all possible substrings? i mean to get ct and ctgcact???
    I m,ust not understand.
    Odi posted an expression that gets 'ct'.
    You posted an expression that gets 'ctgcact'.
    If you want both results, use both expressions.


     

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data. Maybe 5 or 10 rows would be enough in this case.
    Explain, using specific examples, how you get those results from that data.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}

Legend

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