13 Replies Latest reply: Jan 17, 2013 7:32 AM by BluShadow RSS

    regexp_replace issue

    907442
      hi Experts,

      Below is my query :
      SELECT REPLACE('(ABCD)',')','') FROM dual;
      
      
      Ouput is :
      (ABCD
      But i need to replace two brackets with blank spces.
      Output should be like: ABCD
      How to use the REGEXP_REPLACE to get the above Result.


      Thanks,
        • 1. Re: regexp_replace issue
          chris227
          SELECT rtrim(ltrim('(ABCD)','('),')') FROM dual;
          
          or
          
          SELECT regexp_replace('(ABCD)','\(|\)') FROM dual;
          
          which would replace all occurences of ( and )
          
          and this only left occurency of ( and right of )
          
          SELECT regexp_replace('(ABCD)','^\(+|\)+$') FROM dual;
          
          ahh and now to your question :-)
          
          SELECT regexp_replace('(ABCD)','^\(+(.*)\)+$',' \1 ') FROM dual;
          Edited by: chris227 on 17.01.2013 04:37

          Edited by: chris227 on 17.01.2013 04:39

          Edited by: chris227 on 17.01.2013 04:43
          • 2. Re: regexp_replace issue
            avish16
            SELECT translate('(ABCD)','[()]',' ') FROM dual;

            there are 3 spaces at the end.

            Edited by: avish16 on Jan 17, 2013 6:18 PM
            • 3. Re: regexp_replace issue
              BluShadow
              or
              SQL> ed
              Wrote file afiedt.buf
              
                1* SELECT regexp_replace('(ABCD)','[()]') FROM dual
              SQL> /
              
              REGE
              ----
              ABCD
              • 4. Re: regexp_replace issue
                jeneesh
                select substr(your_column,2,length(your_column)-2) str from your)table;
                • 5. Re: regexp_replace issue
                  jeneesh
                  Or with a single function
                  select translate('(ABCD)','()','  ') str from dual;
                  
                  STR
                  ----
                  ABCD
                  • 6. Re: regexp_replace issue
                    BluShadow
                    avish16 wrote:
                    SELECT translate('(ABCD)','()',' ') FROM dual;
                    This is not a correct solution. Brackets have a special meaning in regular expressions unless they are escaped (using '\') or included in a set of characters (using [ and ]).
                    Also, you regular expression does not include an "or" condition.
                    • 7. Re: regexp_replace issue
                      jeneesh
                      BluShadow wrote:
                      avish16 wrote:
                      SELECT translate('(ABCD)','()',' ') FROM dual;
                      This is not a correct solution. Brackets have a special meaning in regular expressions unless they are escaped (using '\') or included in a set of characters (using [ and ]).
                      Also, you regular expression does not include an "or" condition.
                      ? I am confused..

                      Translate is not a REGEXP at all..!
                      • 8. Re: regexp_replace issue
                        chris227
                        It's translate not regexp and it replaces all occurencies like yours (and most of others) does. And didnt replace it with blanks either :-)
                        • 9. Re: regexp_replace issue
                          907442
                          Thanks to all for giving quick response...
                          • 10. Re: regexp_replace issue
                            chris227
                            Think about your choice of the correct answer
                            1. You waned to replace the outer brackets, didnt you?
                            Take a look:
                            SELECT
                             regexp_replace('(A(B(C(D)','[()]')
                             str
                            FROM dual;
                            
                            STR
                            ABCD
                            All inner brackets have gone.
                            But if this is what you wanted, there is no reason for regexp_replace, just take the translate.
                            SELECT
                             ' '||translate('(ABCD)','A()', 'A')||' '
                             str
                            FROM dual;
                            
                            
                            For the outer brackets i still prefer the trim method
                            
                            SELECT
                             ' '||rtrim(ltrim('(ABCD)','('),')')||' '
                             str
                            FROM dual;
                            
                            STR
                             ABCD 
                            However
                            • 11. Re: regexp_replace issue
                              BluShadow
                              jeneesh wrote:
                              BluShadow wrote:
                              avish16 wrote:
                              SELECT translate('(ABCD)','()',' ') FROM dual;
                              This is not a correct solution. Brackets have a special meaning in regular expressions unless they are escaped (using '\') or included in a set of characters (using [ and ]).
                              Also, you regular expression does not include an "or" condition.
                              ? I am confused..

                              Translate is not a REGEXP at all..!
                              I missed it was translate. Even then it wasn't working properly, as it was replacing the opening bracket with a space and removing the closing bracket. Inconsistent.

                              An now I see he's edited it to include the regular expression set, but it's still a translate. sigh people should test before they post.
                              • 12. Re: regexp_replace issue
                                BluShadow
                                chris227 wrote:
                                Think about your choice of the correct answer
                                1. You waned to replace the outer brackets, didnt you?
                                He didn't actually say that. He just said he wanted to replace the brackets... nothing about "outer" brackets at all in the original question.
                                • 13. Re: regexp_replace issue
                                  BluShadow
                                  chris227 wrote:
                                  But if this is what you wanted, there is no reason for regexp_replace, just take the translate.
                                  True, but the OP did ask how to do it with REGEXP_REPLACE.