Split string using new line character and append strings using PL SQL function

Question

I have a scenario of splitting a string field based on newline character chr(10) using pl sql function with a loop and append a tag to the splitted strings(even if there is a empty string in a line).

eg- 1chr(10) 2chr(10) chr(10) 3  should become <a>1</a><a>2</a><a></a><a>3</a>

I have achieved it using an sql query in the following manner

   Select '<test>'
           || replace('1chr(10) 2chr(10) chr(10)3',chr(10),'</test>'||chr(10)||'<test>') 
           || '</test>'
    From your_table;

But I want to get it done using PLSQL function for future uses and if I want to add any other logics. How can I do it using a loop in a plsql function?

Popeye · Accepted Answer · 2020-12-16 18:00:39Z

1

You can use for loop and regexp as follows:

Declare
  Str varchar2(1000) := '1' || chr(10) || '2' || chr(10) || '3';
  Outstr varchar2(2000);
Begin
  For i in 1.. (regexp_count(str, chr(10)) + 1) 
  loop
     Outstr := outstr ||  '<a>' || regexp_substr(str,  '[^'||chr(10)||']',1,i) || '</a>';
  End loop;
  Dbms_output.put_line(outstr);
End;
/

Db<>fiddle

edited Dec 16, 2020 at 18:00

answered Dec 16, 2020 at 17:44

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Johns smith88 Over a year ago

Hi I get the following error " ORA-06550 PKS-00103 encountered symbol LOOK when expecting the following . (), * %="

Johns smith88 Over a year ago

ORA-06550 PKS-00103 encountered symbol "LOOP" when expecting the following . (), * %="

Popeye Over a year ago

Done. Updated the answer and added the db<>fiddle. Please check now!!

Johns smith88 Over a year ago

thanks but when I added blank line. i.e Str varchar2(1000) := '1' || chr(10) || '2' || chr(10) || chr(10)|| '3'; between 2 and 3 I need tags with empty value but empty tags are shown after 3 and not between 2 and 3. How can I change this to catch that empty line?

Johns smith88 Over a year ago

wow your a super genious thanks for the great help

|

Collectives™ on Stack Overflow

Split string using new line character and append strings using PL SQL function

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related