Parsing in Oracle SQL

Hi,

I need help with parsing a column in Oracle.  Please see attached is the example of the current dataset and expected outcome. In the attached spreadsheet,"DIFFCOLS" column stores data separated in a new line and with colons(:). I need them parsed into 3 separate columns(Column name, src and tgt) and rows based on the serial number that is key value. Please see attached is the example of the current dataset and expected outcome.

Thank you
sample.xlsx
angel7170Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Try this.  Change tab1 to your table.

select serial_number,
	regexp_substr(mystring,'[^:]+',1,1) column_name,
	regexp_substr(mystring,'[^:]+',1,2) src,
	regexp_substr(mystring,'[^:]+',1,3) tgt
from (
	select serial_number,
		regexp_substr(diffcols,
					'[^' || chr(10) || ']+',
					1,
					column_value)
			mystring
	from tab1,
		table(select collect(level)
				from dual
			connect by level <= length(diffcols) - length(replace(diffcols, chr(10))) + 1)
)
/

Open in new window

0
 
angel7170Author Commented:
Worked as expected. Thank you so much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.