This discussion is archived
3 Replies Latest reply: Jul 19, 2012 2:31 PM by 950594 RSS

REGEXP_REPLACE question

950594 Newbie
Currently Being Moderated
Hi,

I have a string that looks like:

IF
ANY( NGHD, 4467, 4466, 4570, 4120, 4160, 4451, 4360, 4440, 4610, 4390, 4430, 4420 )

and I need to be able to change it so that it looks like

IF NGHD IN(4467, 4466, 4570, 4120, 4160, 4451, 4360, 4440, 4610, 4390, 4430, 4420 )

My problem is that there may or may not always be a carriage return between the IF and the ANY in the input string, and the variable name after the first bracket won't always be the same. This is for a package that is translating spss syntax to plsql and I have been trying to come up with a way to do this using regexp_replace or some variation but so far haven't been having any luck. Any help with this would be appreciated.
  • 1. Re: REGEXP_REPLACE question
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, and the results you want from that data. since this is your first thread, I'll do it for you:
    CREATE TABLE     table_x
    (       x_id     NUMBER
    ,     str     VARCHAR2 (100)
    );
    
    INSERT INTO table_x (x_id, str) VALUES ( 1, 'IF
    ANY ( NGHD, 4467, 4466)');
    INSERT INTO table_x (x_id, str) VALUES ( 2, 'IF ANY(FOO, 4451)');
    INSERT INTO table_x (x_id, str) VALUES (91, 'ANY (BAR, 4430, 4420)');
    INSERT INTO table_x (x_id, str) VALUES (92, 'IF ANY (4440, 4610)');
    The desired results might be:
    STR                                 NEW_STR
    ----------------------------------- -----------------------------------
    IF                                  IF
    ANY ( NGHD, 4467, 4466)              NGHD IN ( 4467, 4466)
    
    IF ANY(FOO, 4451)                   IF  FOO IN ( 4451)
    ANY (BAR, 4430, 4420)               ANY (BAR, 4430, 4420)
    IF ANY (4440, 4610)                 IF ANY (4440, 4610)
    Always say which version of Oracle you're using. The query below uses PERL-influenced expressions, which were introduced in Oracle 10.2, but it can be re-written to work in Oracle 10.1.

    This does what you requested:
    SELECT       str
    ,       REGEXP_REPLACE ( str
                      , '(IF\s+)ANY\s*\(\s*(\D+)\s*,'
                    , '\1 \2 IN ('
                    )     AS new_str
    FROM      table_x
    ORDER BY  x_id
    ;
    Always explain, with specific examples, how you get the results you want from the given data.
    The query above looks for
    (a) the word IF followed by 1 or more whitespace characters. This is backreference \1.
    (b) Immediately after that, it expects to see the keyword ANY followed by 0 or more whitespace characters and a left (. This part will be thrown away.
    (c) Immediately after that, it expects to see 1 or more non-digits. This is backreference \2.
    (d) Finally, it needs 0 or more whitespace characters, then a comma. This part will be thrown away, too.
    REGEXP_REPLACE replaces (a) through (d) with the parts found in (a) and (c), followed by the keyword IN, and a left ( to replace the one thrown away in part (b).

    It doesn't matter if any of the whitespace includes carriage returns.
    Nothing is changed if the keyword IF is missing (x_id=91), or if the first token inside the parentheses is not composed entirely of non-digits (x_id=92).
    Depending on your exact requirements, you may need to adjust this solution.

    Edited by: Frank Kulash on Jul 19, 2012 2:11 PM
    Added explanation.
  • 2. Re: REGEXP_REPLACE question
    Solomon Yakobson Guru
    Currently Being Moderated
    Oops, I messed up first time. Here is corrected version:
    with t as (
               select 'IF 
    ANY( NGHD, 4467, 4466, 4570, 4120, 4160, 4451, 4360, 4440, 4610, 4390, 4430, 4420 )'str from dual union all
               select 'IF 
    ANY( XYZ, 4467, 4466, 4570, 4120, 4160, 4451, 4360, 4440, 4610, 4390, 4430, 4420 )'str from dual union all
               select 'IF ANY( NGHD, 4467, 4466, 4570, 4120, 4160, 4451, 4360, 4440, 4610, 4390, 4430, 4420 )'str from dual union all
               select 'IF ANY( XYZ, 4467, 4466, 4570, 4120, 4160, 4451, 4360, 4440, 4610, 4390, 4430, 4420 )'str from dual
              )
    select  regexp_replace(replace(str,chr(10),' '),'IF ANY\(([^,]+),','IF \1 IN(') from t
    /
    
    REGEXP_REPLACE(REPLACE(STR,CHR(10),''),'IFANY\(([^,]+),','IF\1IN(')
    --------------------------------------------------------------------------------------
    IF  NGHD IN( 4467, 4466, 4570, 4120, 4160, 4451, 4360, 4440, 4610, 4390, 4430, 4420 )
    IF  XYZ IN( 4467, 4466, 4570, 4120, 4160, 4451, 4360, 4440, 4610, 4390, 4430, 4420 )
    IF  NGHD IN( 4467, 4466, 4570, 4120, 4160, 4451, 4360, 4440, 4610, 4390, 4430, 4420 )
    IF  XYZ IN( 4467, 4466, 4570, 4120, 4160, 4451, 4360, 4440, 4610, 4390, 4430, 4420 )
    
    SQL> 
    SY.

    Edited by: Solomon Yakobson on Jul 19, 2012 2:09 PM
  • 3. Re: REGEXP_REPLACE question
    950594 Newbie
    Currently Being Moderated
    Thanks for the advice, and the answer to my question. The explanation of how the query is getting the results is especially helpful as I've only been working with Oracle for about 6 months (was in an MSSql shop for 5 years prior).

Legend

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