Replace all CRLF with \r\n using REXEXP

I have a process that needs to strip all CRLF from SQL statements and store them with literal characters \r\n and stores this in a .json file. Later, this process is reversed when the .json file is inserted back into the database.

I am using ORACLE 19c.

The original (abbreviated) SQL might look like this:

SELECT SPRIDEN_ID, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME
FROM sztdhof
LEFT JOIN SPRIDEN
ON sztdhof.SZTDHOF_PIDM = spriden.SPRIDEN_PIDM
RIGHT JOIN SSBSECT
ON sztdhof_CRN = SSBSECT_CRN 
WHERE spriden_change_ind IS NULL

The resulting string should look like this:

SELECT SPRIDEN_ID, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME\r\nFROM sztdhof\r\nLEFT JOIN SPRIDEN\r\nON sztdhof.SZTDHOF_PIDM = spriden.SPRIDEN_PIDM\r\nRIGHT JOIN SSBSECT\r\nON sztdhof_CRN = SSBSECT_CRN \r\nWHERE spriden_change_ind IS NULL

I have tried numerous ways to get ORACLE REGEXP_REPLACE to recognize the carriage return and line feed together. None seem to work.

To start, I read the value from the db into a variable v_text. Then I attempt the conversion and placing the result into a second variable v_outtext. If I use two separate statements, each works separately. And I can live with that method, but I would like this to work as a single statement. (Note. I have also tried the REPLACE function with similar results.)

This code...

SELECT REPLACE (v_text, chr(13) , '\r\n')
    INTO v_outtext
    FROM dual;

produces...

SELECT SPRIDEN_ID, SPRIDEN_LAST_NAME, SPRIDEN_FIRST_NAME\r\n
FROM sztdhof\r\n
LEFT JOIN SPRIDEN\r\n
ON sztdhof.SZTDHOF_PIDM = spriden.SPRIDEN_PIDM\r\n
RIGHT JOIN SSBSECT\r\n
ON sztdhof_CRN = SSBSECT_CRN \r\n
WHERE spriden_change_ind IS NULL\r\n

SELECT REGEXP_REPLACE(v_text, chr(10) , '\r\n') --is similar to above, except the \r\n are at the front of each line.

SELECT REGEXP_REPLACE(v_text, (chr(13)chr(10) , '\r\n') gives me an error, missing right parenthesis.

SELECT REGEXP_REPLACE (v_text, '(\r\n)', '\r\n') replaces r with \r and n with \n, but only the first instance.

I have tried numerous iterations. None solve the problem. Any thoughts would be welcome.

CodePudding user response：

You need to concatenate the two characters you want to replace, not just put them in parentheses (even if you supplied both):

SELECT REPLACE(v_text, chr(13) || chr(10) , '\r\n')
INTO v_outtext
FROM dual;

If if it's already in a PL/SQL variable you don't need to to the context switch to select from dual; you can simplify to:

v_outtext := REPLACE(v_text, chr(13) || chr(10) , '\r\n');

but it sounds like you can use the first method to read the value from the database into v_outtext in one go, without the intermediate v_text.

db<>fiddle