Forum Stats

  • 3,734,283 Users
  • 2,246,938 Discussions
  • 7,857,218 Comments

Discussions

Split string field and append new values to the splitted strings

User_1KK7K
User_1KK7K Member Posts: 6 Red Ribbon
edited Dec 17, 2020 10:27AM in SQL & PL/SQL

Hi,


I am having scenario where I want to split a single string based on the new line character and append a tag.(If blank string in a line tags with empty string should be there)


eg-

Input =

'12 abc' || chr(10) || '3' || chr(10) ||chr(10) || '3'

Output=

<a>12 abc</a><a>3</a><a></a><a>3</a>


I have written a function to achieve this as follows

Declare

  Str varchar2(1000) := '12' || chr(10) || '3' || chr(10) ||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;


But the result I get is

<a>12 abc

3

3</a><a></a><a></a><a></a>


How can I fix this issue(I dont want to use any select statement either only loop)?

Tagged:

Best Answer

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,512 Red Diamond

    Hi,

    In case you're wondering why this failed

    Outstr := outstr || '<a>' || regexp_substr(str, '[^,]+||chr(10)',1,i) || '</a>';

    the main reason is that you included '||chr(10)' in the string literal.

    'XYZ||chr(10)'
    

    is a 12-character string, where the 4th and 5th characters are pipes, the 6th character is a lower-case 'c', and so on. That's differe t from a 4-character string, where the 5th character is CHR(10).

    You could make the anonymous block (it's not a function) work by changing the expression inside the loop to:

    Outstr := outstr || '<a>'
                     || regexp_substr ( str
       	 	    		 , '([^' || CHR (10) || ']*)(' || CHR (10) || '|$)'
    				 , 1
    				 , i
    				 , NULL
    				 , 1
    				 )
    		 || '</a>';
    

    Again, I don't recommend doing this. Use plain old REPLACE, as shown in my earlier reply.

    User_1KK7K
Sign In or Register to comment.