This discussion is archived
11 Replies Latest reply: Feb 24, 2013 8:43 PM by Most Wanted!!!! RSS

query need regexp_replace

gopi130 Newbie
Currently Being Moderated
Hi Friends,
I need query for below my request

This is our sample text
Original text: ,UNDRLYNG_AST_NM,"NEXTERA ENERGY, INC.",


output Replace text:,UNDRLYNG_AST_NM,"NEXTERA ENERGY~ INC."

i want replace ',' to '~' between the alphabetic or alphabetic with space
  • 1. Re: query need regexp_replace
    Most Wanted!!!! Journeyer
    Currently Being Moderated
    try this
    SELECT REGEXP_REPLACE (a, '([[:space:],])', '~')
      FROM (SELECT 'UNDRLYNG_AST_NM,"NEXTERA ENERGY, INC."' AS a
              FROM DUAL)
    SELECT REGEXP_REPLACE (a, ', ', '~')
      FROM (SELECT 'UNDRLYNG_AST_NM,"NEXTERA ENERGY, INC."' AS a
              FROM DUAL)
    SELECT REGEXP_REPLACE (a, '(,[[:space:]])', '~')
      FROM (SELECT 'UNDRLYNG_AST_NM,"NEXTERA ENERGY, INC."' AS a
              FROM DUAL)
    
    
    Output:
    UNDRLYNG_AST_NM,"NEXTERA ENERGY~INC."
    Regards,
    friend

    Edited by: most wanted!!!! on Feb 18, 2013 12:33 AM

    Edited by: most wanted!!!! on Feb 18, 2013 12:36 AM

    Edited by: most wanted!!!! on Feb 18, 2013 12:56 AM
  • 2. Re: query need regexp_replace
    jeneesh Guru
    Currently Being Moderated
    gopi130 wrote:
    i want replace ',' to '~' between the alphabetic or alphabetic with space
    regexp_replace(str,'([[:alpha:]]+ *),( *[[:alpha:]]+)','\1~\2')
    
    with t as
    (select ',UNDRLYNG_AST_NM,"NEXTERA ENERGY, INC."' str
    from dual
    )
    select str,
           regexp_replace(str,'([[:alpha:]]+ *),( *[[:alpha:]]+)','\1~\2') str2
    from t;
    
    STR                                     STR2
    --------------------------------------- ---------------------------------
    ,UNDRLYNG_AST_NM,"NEXTERA ENERGY, INC." ,UNDRLYNG_AST_NM,"NEXTERA ENERGY~ INC."
    Edited by: jeneesh on Feb 18, 2013 2:05 PM
  • 3. Re: query need regexp_replace
    Rahul_India Journeyer
    Currently Being Moderated
    For better performance and another way try this ;)
    with t as
    
    (
    select ',UNDRLYNG_AST_NM,"NEXTERA ENERGY, INC.",' as str from dual
    )
    select substr(str,1,instr(str,',',-1,2)-1)||
    replace(substr(str,instr(str,',',-1,2),1),',','~')||
    substr(str,instr(str,',',-1,2)+1)  
    
    as string_test from t
    OUTPUT
    *STRING_TEST*
    ,UNDRLYNG_AST_NM,"NEXTERA ENERGY~INC.",
    Edited by: Rahul India on Feb 18, 2013 2:13 PM
  • 4. Re: query need regexp_replace
    jeneesh Guru
    Currently Being Moderated
    Rahul India wrote:
    For better performance and another way try this ;)
    Is your query generic for the requirement?

    gopi130 wrote:
    i want replace ',' to '~' between the alphabetic or alphabetic with space
    Edited by: jeneesh on Feb 18, 2013 2:14 PM
  • 5. Re: query need regexp_replace
    Most Wanted!!!! Journeyer
    Currently Being Moderated
    rahul said...
    For better performance and
    rahul FYI
    --jeneesh query
    EXPLAIN PLAN FOR
    WITH t AS
         (SELECT ',UNDRLYNG_AST_NM,"NEXTERA ENERGY, INC."' str
            FROM DUAL)
    SELECT str,
           REGEXP_REPLACE (str,
                           '([[:alpha:]]+ *),( *[[:alpha:]]+ *)',
                           '\1~\2'
                          ) str2
      FROM t;
    SELECT *
      FROM TABLE (DBMS_XPLAN.display ());
      
      
    Explain complete.
    
    PLAN_TABLE_OUTPUT                                                               
    --------------------------------------------------------------------------------
                                                                                    
    ------------------------------------------------------                          
    | Id  | Operation        | Name | Rows  | Cost (%CPU)|                          
    ------------------------------------------------------                          
    |   0 | SELECT STATEMENT |      |     1 |     2   (0)|                          
    |   1 |  FAST DUAL       |      |     1 |     2   (0)|                          
    ------------------------------------------------------                          
                                                                                    
    Note                                                                            
    -----                                                                           
       - 'PLAN_TABLE' is old version                                                
    
    
    11 rows selected.
    
    
    --yours
    EXPLAIN PLAN FOR
    WITH t AS
         (SELECT ',UNDRLYNG_AST_NM,"NEXTERA ENERGY, INC.",' AS str
            FROM DUAL)
    SELECT    SUBSTR (str, 1, INSTR (str, ',', -1, 2) - 1)
           || REPLACE (SUBSTR (str, INSTR (str, ',', -1, 2), 1), ',', '~')
           || SUBSTR (str, INSTR (str, ',', -1, 2) + 1) AS string_test
      FROM t;
    SELECT *
      FROM TABLE (DBMS_XPLAN.display ());
      
      
    Explain complete.
    
    PLAN_TABLE_OUTPUT                                                               
    --------------------------------------------------------------------------------
                                                                                    
    ------------------------------------------------------                          
    | Id  | Operation        | Name | Rows  | Cost (%CPU)|                          
    ------------------------------------------------------                          
    |   0 | SELECT STATEMENT |      |     1 |     2   (0)|                          
    |   1 |  FAST DUAL       |      |     1 |     2   (0)|                          
    ------------------------------------------------------                          
                                                                                    
    Note                                                                            
    -----                                                                           
       - 'PLAN_TABLE' is old version                                                
    
    
    11 rows selected.  
      
    
    --mine
    EXPLAIN PLAN FOR
    SELECT REGEXP_REPLACE (a, '(,[[:space:]])', '~')
      FROM (SELECT 'UNDRLYNG_AST_NM,"NEXTERA ENERGY, INC."' AS a
              FROM DUAL);
    SELECT *
      FROM TABLE (DBMS_XPLAN.display ());
      
    Explain complete.
    
    PLAN_TABLE_OUTPUT                                                               
    --------------------------------------------------------------------------------
                                                                                    
    ------------------------------------------------------                          
    | Id  | Operation        | Name | Rows  | Cost (%CPU)|                          
    ------------------------------------------------------                          
    |   0 | SELECT STATEMENT |      |     1 |     2   (0)|                          
    |   1 |  FAST DUAL       |      |     1 |     2   (0)|                          
    ------------------------------------------------------                          
                                                                                    
    Note                                                                            
    -----                                                                           
       - 'PLAN_TABLE' is old version                                                
    
    
    11 rows selected.
    regards,
    friend

    Edited by: most wanted!!!! on Feb 18, 2013 12:46 AM

    Edited by: most wanted!!!! on Feb 18, 2013 12:47 AM
  • 6. Re: query need regexp_replace
    Rahul_India Journeyer
    Currently Being Moderated
    jeneesh wrote:
    Rahul India wrote:
    For better performance and another way try this ;)
    Is your query generic for the requirement?
    I wrote for the query for the last comma occuring just before INC.So i guess no.
    Didn't read the requirement :p
  • 7. Re: query need regexp_replace
    jeneesh Guru
    Currently Being Moderated
    There is no meaning in bench marking like this..

    REGEXP are more CPU intensive, and so will be less performing normally compared to the variants using normal string functions..

    And all the queries wont give similar results..

    Rahul's query wont give the required output if the string is changed as mentioned in the previous post...

    And in your query, you are using OPs expected output as the input, by mistake..
  • 8. Re: query need regexp_replace
    Rahul_India Journeyer
    Currently Being Moderated
    Use large data set
  • 9. Re: query need regexp_replace
    Most Wanted!!!! Journeyer
    Currently Being Moderated
    jeneesh wrote
    you are using OPs expected output as the input, by mistake.. 
    oh i didn't see that thank you
    REGEXP are more CPU intensive, and so will be less performing normally compared to the variants using normal string functions..
    
    And all the queries wont give similar results
    lesson learned :) thank you.


    regards
    friend
  • 10. Re: query need regexp_replace
    Nicosa Expert
    Currently Being Moderated
    Hi Most Wanted,
    most wanted!!!! wrote:
    PLAN_TABLE_OUTPUT                                                               
    --------------------------------------------------------------------------------
    
    ------------------------------------------------------                          
    | Id  | Operation        | Name | Rows  | Cost (%CPU)|                          
    ------------------------------------------------------                          
    |   0 | SELECT STATEMENT |      |     1 |     2   (0)|                          
    |   1 |  FAST DUAL       |      |     1 |     2   (0)|                          
    ------------------------------------------------------                          
    
    Note                                                                            
    -----                                                                           
    - 'PLAN_TABLE' is old version                                                
    You might consider this also :
    drop table plan_table;
    That would let explain plan naturaly use the sys.plan_table$.
  • 11. Re: query need regexp_replace
    Most Wanted!!!! Journeyer
    Currently Being Moderated
    oh thank you :)

Legend

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