How to replace string present on next line

Question

I want to replace the string Test string with "Hello World". I want Hello to be present on first line and World should be present on second line as present in the Main string below

" This is a Test
string. This is a Test2 string"

The output should be like

" This is a Hello
World. This is a Test2 string"

Also it should be case insensitive while replacing

Please suggest a solution in Oracle SQL.

I tried this query. It is for replacing if "Test string" present on the same line. And it satisfy case insensitive

SELECT REGEXP_REPLACE ('This is a Test string. This is a Test2 string',
                    'Test string',
                    'Hello World',
                   1,
                   0,
                   'i')  FROM DUAL;

Answer 1

You can use the regex below to get your desired result.

The expression Test(\W*)string will match test, then any non word characters (in this case spaces and new lines), then the word string. It does not match Test2 string because the 2 is a word character. The parenthesis are necessary for the next parameter.

Hello\1World will place Hello World in place of test string but putting the \1 between hello and world will retain any characters that were between the test and string when the expression was matched.

SELECT REGEXP_REPLACE ('This is a Test 
 string. This is a Test2 string',
                       'Test(\W*)string',
                       'Hello\1World',
                       1,
                       0,
                       'i') as result
  FROM DUAL;


                                            RESULT
__________________________________________________
This is a Hello
 World. This is a Test2 string