• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 35
  • Last Modified:

Splitting values into their own record

In oracle if I have a table called "Member diagnosis" that has a column called "diagnosis code"  with a value of d1|d2|d3|d4.  What is the syntax to split out each diagnosis code into its own record?

Thanks
0
Maliki Hassani
Asked:
Maliki Hassani
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Try this:
select
	regexp_substr(diagnosis_code,'[^|]+',1,1) d1,
	regexp_substr(diagnosis_code,'[^|]+',1,2) d2,
	regexp_substr(diagnosis_code,'[^|]+',1,3) d3,
	regexp_substr(diagnosis_code,'[^|]+',1,4) d4
from member_diagnosis
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
If you want them as rows instead of columns, try:
select 
	regexp_substr(diagnosis_code, '[^|]+', 1, column_value) diagnosis_code
from member_diagnosis,
	table(select collect(level)
				from dual
			connect by level <= length(diagnosis_code) - length(replace(diagnosis_code, '|')) + 1)
/

Open in new window

0
 
Maliki HassaniAuthor Commented:
Thank you, sir
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now