Split string field and append new values to the splitted strings

User_1KK7K

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

Frank Kulash

Hi,

You don't need regular expressions (or PL/SQL) for that:

'<a>' || REPLACE ( str
   	 	 , CHR (10)
		 , '</a><a>'
		 )
      || '</a>'

does the whole job without a loop. Of course, you could use REGEXP_REPLACE instead of REPLACE, but that would be slower.

Frank Kulash

Hi,

You don't need regular expressions (or PL/SQL) for that:

'<a>' || REPLACE ( str
   	 	 , CHR (10)
		 , '</a><a>'
		 )
      || '</a>'

does the whole job without a loop. Of course, you could use REGEXP_REPLACE instead of REPLACE, but that would be slower.

Frank Kulash

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

https://community.oracle.com/tech/developers/discussion/comment/16778823#Comment_16778823

noted. Thanks alot