How to achieve this.

user1740166
    Hi,

    I want your help with this.


    existing output of the query is:

    error_desc
    --------------
    cannot create a record for AA12345 as the user name for (1234567890) cannot be found.
    cannot create a record for AA11234 as the user name for (1224567890) cannot be found.
    cannot create a record for AA12245 as the user name for (1334567890) cannot be found.
    cannot create a record for AA13345 as node for (1244567890) cannot be found.
    cannot create a record for AA12445 as node for (1235567890) cannot be found.
    cannot create a record for AA12355 as node for (1234667890) cannot be found.

    from this, I need the output as in two different columns:

    ID VALUE
    --- -----------
    AA12345 1234567890
    AA11234 1224567890
    AA12245 1334567890
    AA13345 1244567890
    AA12445 1235567890
    AA12355 1234667890

    I've tried using SUBSTR function, but it doesn't help as the position from the 4th records has changed for the VALUE.

    Please help.
      • 1. Re: How to achieve this.
        MichaelS
        Maybe
        SQL> with t as (
         select 'cannot create a record for AA12345 as the user name for (1234567890) cannot be found.' error_desc from dual union all
         select 'cannot create a record for AA11234 as the user name for (1224567890) cannot be found.' from dual union all
         select 'cannot create a record for AA12245 as the user name for (1334567890) cannot be found.' from dual union all
         select 'cannot create a record for AA13345 as node for (1244567890) cannot be found.' from dual union all
         select 'cannot create a record for AA12445 as node for (1235567890) cannot be found.' from dual union all
         select 'cannot create a record for AA12355 as node for (1234667890) cannot be found.' from dual
        )
        --
        --
        select regexp_substr(error_desc,'AA[[:digit:]]+') id,
                 regexp_substr(error_desc,'[[:digit:]]+',1,2) value  
         from t
        /
        ID         VALUE     
        ---------- ----------
        AA12345    1234567890
        AA11234    1224567890
        AA12245    1334567890
        AA13345    1244567890
        AA12445    1235567890
        AA12355    1234667890
        
        6 rows selected.
        • 2. Re: How to achieve this.
          user1740166
          Hi Micheal,

          Looks like this will work..

          I will test it and let you know.

          Thank you very much.
          • 3. Re: How to achieve this.
            Frank Kulash
            Hi,

            Does the original query build the strings by concatenating various columns, including the two you want? If so, you should modify it to include those two columns.

            If all you can do is query those results, then you could use INSTR to find where the parentheses are, and use the values that INSTR returns in SUBSTR.
            If you're using Oracle 10 (or higher) it's easier to use regular expressions, like this:
            SELECT     REGEXP_SUBSTR ( error_desc
                            , '[^ ]+'
                            ,     1
                            , 6
                            )               AS id
            ,     REGEXP_REPLACE ( error_desc
                             , '^.*\('     ||     -- everything up to (
                               '(.+)          ||     -- \1 = everything inside parentheses
                            '\).*$'          -- ) and rest of string
                             , '\1'
                             )          AS value_col          -- value is not a good name
            FROM     table_x;
            • 4. Re: How to achieve this.
              user1740166
              that works good..

              but how about for this?

              cannot create a record for AA12345 as the user name for (1234567890) cannot be found.
              cannot create a record for AA11234 as the user name for (1224567890) cannot be found.
              cannot create a record for AA12245 as the user name for (1334567890) cannot be found.
              cannot create a record for AA13345 as node for (1244567890) cannot be found.
              cannot create a record for AA12445 as node for (1235567890) cannot be found.
              cannot create a record for 1234567 as node for (1234667890) cannot be found.
              cannot create a record for 1224567 as node for (1234667890) cannot be found.
              cannot create a record for 1334567 as node for (1234667890) cannot be found.

              I want the output as same.

              Thanks in advance.
              • 5. Re: How to achieve this.
                MichaelS
                SQL>  with t as (
                 select 'cannot create a record for AA12345 as the user name for (1234567890) cannot be found.' error_desc
                 from dual union all
                 select 'cannot create a record for AA11234 as the user name for (1224567890) cannot be found.' from dual union all
                 select 'cannot create a record for AA12245 as the user name for (1334567890) cannot be found.' from dual union all
                 select 'cannot create a record for AA13345 as node for (1244567890) cannot be found.' from dual union all
                 select 'cannot create a record for AA12445 as node for (1235567890) cannot be found.' from dual union all
                 select 'cannot create a record for 1234567 as node for (1234667890) cannot be found.' from dual union all
                 select 'cannot create a record for 1224567 as node for (1234667890) cannot be found.' from dual union all
                 select 'cannot create a record for 1334567 as node for (1234667890) cannot be found.' from dual
                )
                --
                --
                select regexp_substr(error_desc,'\d+|AA\d+') id,
                         regexp_substr(error_desc,'\d+',1,2) value  
                 from t
                
                ID         VALUE     
                ---------- ----------
                AA12345    1234567890
                AA11234    1224567890
                AA12245    1334567890
                AA13345    1244567890
                AA12445    1235567890
                1234567    1234667890
                1224567    1234667890
                1334567    1234667890
                
                8 rows selected.
                Please post the most generic samples to cope for all possibilities if above solution still not suffices.
                • 6. Re: How to achieve this.
                  user1740166
                  This works Michael.

                  You are awesome!

                  Thanks a lot.
                  • 7. Re: How to achieve this.
                    user1740166
                    Hi Micheal,

                    This works good in 10g as you used regular expressions.

                    Can you please give me the query for 9i, which basically should do the same.

                    Thanks again..
                    • 8. Re: How to achieve this.
                      ravikumar.sv
                      with t as (
                       select 'cannot create a record for AA12345 as the user name for (1234567890) cannot be found.' error_desc
                       from dual union all
                       select 'cannot create a record for AA11234 as the user name for (1224567890) cannot be found.' from dual union all
                       select 'cannot create a record for AA12245 as the user name for (1334567890) cannot be found.' from dual union all
                       select 'cannot create a record for AA13345 as node for (1244567890) cannot be found.' from dual union all
                       select 'cannot create a record for AA12445 as node for (1235567890) cannot be found.' from dual union all
                       select 'cannot create a record for 1234567 as node for (1234667890) cannot be found.' from dual union all
                       select 'cannot create a record for 1224567 as node for (1234667890) cannot be found.' from dual union all
                       select 'cannot create a record for 1334567 as node for (1234667890) cannot be found.' from dual
                      )
                      select substring(error_desc,28,7),substring(error_desc,instr(error_desc,'(',1)+1,10) from t
                      Ravi Kumar