2 Replies Latest reply on Feb 21, 2013 4:50 PM by 992557

    Help with query and REGEXP_REPLACE

    992557
      Hi, i am new in this forum. I have a problem with a query, i have this query :

      SELECT CONDICION
      from cd_capturav2.A31102012condiciones
      where diseno like '%RTN%'
      and FORMULARIO = 'CUADROII7A1'
      and partida = '10101'
      and camporef1 = 1;

      That give to me this line :

      RIESGOTASA|CUADII8RTN|MONERT|=|00||Y|(|RIESGOTASA|CUADII8RTN|CODPROD|=|1411||O||RIESGOTASA|CUADII8RTN|CODPROD|=|14111||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1417||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1418||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1414||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1413||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1416||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1415||O||RIESGOTASA|CUADII8RTN|CODPROD|=|14121||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1412||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1700|)|Y||RIESGOTASA|CUADII8RTN|COEFACTUALIZACION|=|1||Y||RIESGOTASA|CUADII8RTN|CUADRO|<>|7C||||

      In that line i need to extract all the CODPROD in one select that returnt the codprod numbers:
      1411,14111,1417,1418,1414,1413,1416, etc

      I am trying to use the REGEXP_REPLACE function, but idk how i can delete all characters except CODPROD|=|number.

      Thank you for your help !

      PD: Sry for my english, its not my native language.

      Edited by: user12883924 on Feb 21, 2013 8:11 AM
        • 1. Re: Help with query and REGEXP_REPLACE
          Frank Kulash
          Hi,

          Welcome to the forum!
          user12883924 wrote:
          Hi, i am new in this forum.
          See the forum FAQ {message:id=9360002}
          It can really help you to get good solutions quickly.
          I have a problem with a query, i have this query :

          SELECT CONDICION
          from cd_capturav2.A31102012condiciones
          where diseno like '%RTN%'
          and FORMULARIO = 'CUADROII7A1'
          and partida = '10101'
          and camporef1 = 1;

          That give to me this line :

          RIESGOTASA|CUADII8RTN|MONERT|=|00||Y|(|RIESGOTASA|CUADII8RTN|CODPROD|=|1411||O||RIESGOTASA|CUADII8RTN|CODPROD|=|14111||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1417||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1418||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1414||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1413||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1416||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1415||O||RIESGOTASA|CUADII8RTN|CODPROD|=|14121||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1412||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1700|)|Y||RIESGOTASA|CUADII8RTN|COEFACTUALIZACION|=|1||Y||RIESGOTASA|CUADII8RTN|CUADRO|<>|7C||||

          In that line i need to extract all the CODPROD in one select that returnt the codprod numbers:
          1411,14111,1417,1418,1414,1413,1416, etc
          Do you want just a comma-delimited list of numbers, as above, or do you want 'CODPROD|=|' also, as below?
          I am trying to use the REGEXP_REPLACE function, but idk how i can delete all characters except CODPROD|=|number.
          If you want just the numbers:
          WITH     sample_data  AS
          (
               SELECT  'RIESGOTASA|CUADII8RTN|MONERT|=|00||Y|(|RIESGOTASA|CUADII8RTN|CODPROD|=|1411||O||RIESGOTASA|CUADII8RTN|CODPROD|=|14111||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1417||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1418||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1414||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1413||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1416||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1415||O||RIESGOTASA|CUADII8RTN|CODPROD|=|14121||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1412||O||RIESGOTASA|CUADII8RTN|CODPROD|=|1700|)|Y||RIESGOTASA|CUADII8RTN|COEFACTUALIZACION|=|1||Y||RIESGOTASA|CUADII8RTN|CUADRO||7C||||'  AS condicion
               FROM    dual
          )
          SELECT     RTRIM ( REGEXP_REPLACE ( condicion
                                  , '.*?((CODPROD\|=\|(\d+))|$)'
                                , '\3,'
                                )
                      , ','
                      )     AS codprods
          FROM    sample_data
          ;
          Output from the query above:
          CODPRODS
          --------------------------------------------------------
          1411,14111,1417,1418,1414,1413,1416,1415,14121,1412,1700
          Thank you for your help !

          PD: Sry for my english, its not my native language.
          No matter how well you can write in English, it's good to provide clear examples in SQL. Always post a few rows of sample data (using CREATE TABLE and INSERT statements, or a WITH clause, like I did above) and the exact results you want from that data. The less you can rely on explanations in English, the more you have to rely on clear input and output examples.

          Edited by: Frank Kulash on Feb 21, 2013 11:40 AM

          Always say which version of Oracle you're using (for example, 11.2.0.3.0).
          The query above works in Oracle 10 (or higher), but there might be a simpler way in Oracle 11, using the new 6th argument to REGEXP_SUBSTR.
          • 2. Re: Help with query and REGEXP_REPLACE
            992557
            thank you very much, just that i needed :D