How to split text line by line in PL/SQL?

Question

I want to implement a procedure which takes a string consisting of multiple lines. I want to take split the string line by line. And then i want to parse and manipulate the string. I didn't find a built-in method for this. I tried to find the index of \n characters bu it didn't work. My code is shown below:

create or replace
PROCEDURE VFROLLOUT_MULTIPLE(test_var VARCHAR2) AS
BEGIN
  dbms_output.put_line(INSTR('test1
  test2
  test3
  ', '\n'));
  commit;
END ;

It does not work. How to do that and how to split text line by line?

Answer 1

Please use the below query,

SELECT regexp_substr( 'test1  
test2  
test3  
','[[:alnum:]]+',1,level) Result FROM dual connect by level<= regexp_count('test1  
test2  
test3  
',chr(10));

Answer 2

CHR() function might help you.

Remember, different platforms expect different new line characters:

CHR(10) => LF, line feed (unix)

CHR(13) => CR, carriage return (windows, together with line feed)

You can find index for first LF occurence like this:

BEGIN
  dbms_output.put_line(instr('test1
  test2
  test3
  '
                            ,chr(10)
                            ,1));
END;

Also you could replace new lines with commas and use/adapt some of techniques discussed here: Convert comma separated string to array in PL/SQL

Answer 3

How about you try this one...

   Create or replace PROCEDURE VFROLLOUT_MULTIPLE(test_var VARCHAR2 ) 
            AS
            NEW_TEST_VAR VARCHAR2(2000);
            OUT_TEST_VAR VARCHAR2(2000);
            STRING1 VARCHAR2(2000);
            STRING2 VARCHAR2(2000);
            STRING3 VARCHAR2(2000);
         BEGIN 
            SELECT REGEXP_REPLACE(TEST_VAR,'[[:space:]]+',',') INTO NEW_TEST_VAR
                       FROM DUAL; --Replaces the line breaks/spaces with a delimeter
            SELECT 
                    SUBSTR(NEW_TEST_VAR,1,INSTR(NEW_TEST_VAR,',')-1)    AS  STRING1,
                    SUBSTR(NEW_TEST_VAR,INSTR(NEW_TEST_VAR,',',1,1)+1,
                      ( (INSTR(NEW_TEST_VAR,',',1,2)+1)
                       -INSTR(NEW_TEST_VAR,',',1,1)-1 ) -1)   AS STRING2 ,
                    SUBSTR( NEW_TEST_VAR,INSTR(NEW_TEST_VAR,',',1,2)+1 )  AS STRING3
                    INTO STRING1,STRING2,STRING3
            FROM DUAL ; -- Using the delimeter the string is splitted
            DBMS_OUTPUT.PUT_LINE('First Part   : ' || STRING1);
            DBMS_OUTPUT.PUT_LINE('Second Part  : ' || STRING2);
            DBMS_OUTPUT.PUT_LINE('Third Part   : ' || STRING3);  
             --you can perform manipulations using these variables
            OUT_TEST_VAR := STRING1 ||  ' ' || STRING2 || ' ' || STRING3 ;
            DBMS_OUTPUT.PUT_LINE('Full String  : ' || OUT_TEST_VAR );
            END;

--Executing procedure

Answer 4

SELECT REPLACE(regexp_substr(<<YOUR TEXT>>, '[^'|| CHR(10) || ']+', 1, level), CHR(13), '') AS split_value
FROM   DUAL
CONNECT BY regexp_substr(<<YOUR TEXT>>, '[^'|| CHR(10) || ']+', 1, level) IS NOT NULL;

e.g.:

SELECT REPLACE(regexp_substr('test1 test2
test3 test 4
test5', '[^'|| CHR(10) || ']+', 1, level), CHR(13), '') AS split_value
FROM   DUAL
CONNECT BY regexp_substr('test1 test2
test3 test 4
test5', '[^'|| CHR(10) || ']+', 1, level) IS NOT NULL;