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?

"It does not work". Does that mean that INSTR doesn't return the position of the newline or are you not sure how to proceed after getting the position of the newline with INSTR? — ruudvan, Jun 07 '16 at 13:28
Edit your post to show an actual example input string, describe how you will parse and manipulate and include what the output should look like when you are done. In other words, show the bigger picture to provide a better understanding of what it is you are trying to achieve. — Gary_W, Jun 07 '16 at 19:48

score 3 · Answer 1 · edited Jun 07 '16 at 23:21

3

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));

edited Jun 07 '16 at 23:21

Kara

6,115
16
50
57

answered Jun 07 '16 at 13:11

Boobal Ganesan

118
1
1
6

1

Clean and simple! Thanks for saving me some time reinventing the wheel! – StewS2 Apr 19 '18 at 20:05
but it works only when there is a single word in a linem if there is multiple words in one line, it does not work – TheName Jan 12 '21 at 15:22

score 1 · Answer 2 · edited May 23 '17 at 10:29

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

score 0 · Answer 3 · answered Jun 08 '16 at 21:52

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

RMAX · Answer 4 · 2022-05-24T09:34:32.350

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;

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

4 Answers4