regex - Convert Regular Expression To Oracle - Stack Overflow

admin管理员组

文章数量:1123051

I have a string of characters (an CSV file) that contains \r\n (or chr(13)||chr(10) in PLSQL) embedded in the data. I need to ingore these embedded Carriage Return Line Feeds so I can find the actual end of the CSV row.

Example:

This is line 1,"abc\r\nxyz","and, comma, some more","abc\r\n123\r\nzyx",oh more text,"let's see","33,333.01"\r\nThis is line 2,"Hello, 321","Four\r\nand five...\r\nand six",and 123456\r\nThis is line 3

or in PLSQL

'This is line 1,"abc' || chr(13) || chr(10) || 'xyz","and, comma, some more","abc' || chr(13) || chr(10) || '123' || chr(13) || chr(10) || 'zyx",oh more text,"let's see","33,333.01"' || chr(13) || chr(10) || 'This is line 2,"Hello, 321","Four' || chr(13) || chr(10) || 'and five...' || chr(13) || chr(10) || 'and six",and 123456' || chr(13) || chr(10) || 'This is line 3'

Using / I was able to find the following regular expression that works on the first set of text above.

\r\n

this replaces the actual row ending \r\n with the text "CRLF"

When trying the same regex in Oracle, it does not work, using v_TEXT as variable holding the second set of text above.

select REGEXP_REPLACE( v_TEXT, '' || chr(13) || chr(10) || '', 'CRLF' ) from dual;

Can anyone help me convert the regex above to work in Oracle? Or, alternatively, point me to a different regex combo that would do the same work as above (only replace row ending \r\n with CRLF while ignoring \r\n that occurs between double quotes?

The intended out put would be this:

This is line 1,"abc\r\nxyz","and, comma, some more","abc\r\n123\r\nzyx",oh more text,"let's see","33,333.01"CRLFThis is line 2,"Hello, 321","Four\r\nand five...\r\nand six",and 123456CRLFThis is line 3

本文标签： regexConvert Regular Expression To OracleStack Overflow