11 Replies Latest reply: Feb 24, 2013 10:43 PM by Most Wanted!!!! RSS

    query need regexp_replace

    gopi130
      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!!!!
          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
            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
              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
                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!!!!
                  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
                    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
                      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
                        Use large data set
                        • 9. Re: query need regexp_replace
                          Most Wanted!!!!
                          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-Oracle
                            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!!!!
                              oh thank you :)