regexp_replace and newline characters
606667Aug 17 2012 — edited Aug 17 2012I 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 ^