4 Replies Latest reply: Aug 17, 2012 6:08 PM by 606667 RSS

    regexp_replace and newline characters

    606667
      I am using regexp_replace to do some insertion into a string in the following format: "A", "B", "C", "D"... I am inserting a prefix DLJP to each item so the final result would be "DLJPA", "DLJPB", "DLJPC" etc. The regular expression I came up with works just fine however it is stripping out the newline characters in between the string.

      select regexp_replace('"A", "B",' || chr(13) || chr(10) || '"C", "D"', '(^|,)\s*\"', '\1"DLJP') from dual

      Result should be (with line break intact):
      "DLJPA", "DLJPB"
      "DLJPC", "DLJPD"

      but instead result is:
      "DLJPA", "DLJPB", "DLJPC", "DLJPD"

      I want to PRESERVE the original line breaks. I am not sure why it is stripping out the CRLFs because I am not including the CRLFs in the regexp pattern. Per the documentation the ^ is not matched unless I specify 'm' as the match parameter. In any case it removes the line breaks regardless, even if I omit matching ^
        • 1. Re: regexp_replace and newline characters
          damorgan
          Wouldn't a non-regular expression REPLACE do precisely what you want?
          • 2. Re: regexp_replace and newline characters
            606667
            it would if the spaces between would be equal, unfortunately they are random... could be "A", "B", "C" or "A",[space][space] "B", "C" or "A", "B",[space][space][space] "C" etc. etc. the formatting is unpredictable so an exact search and replace is not possible
            • 3. Re: regexp_replace and newline characters
              Solomon Yakobson
              select regexp_replace('"A", "B",' || chr(13) || chr(10) || '"C", "D"','"([^"]+)"','"DLJP\1"') from dual
              /
              
              REGEXP_REPLACE('"A","B",'||CHR(13)|
              -----------------------------------
              "DLJPA", "DLJPB",
              "DLJPC", "DLJPD"
              
              
              SQL>  
              {code}
              
              SY.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
              • 4. Re: regexp_replace and newline characters
                606667
                thanks Solomon :)