Help - Search - Members - Calendar
Full Version: How to create a view by tokenizing a comma delimited column
Oracle DBA Forums > Oracle > Oracle Forum
Sriram
Hello All,

I have a table with comma separated strings as data in one of itís columns. The string is always comma separated but it can have any number of tokens.

For example:

Table: MyOrder

OrderNo SystemName
1000 UPLEX,DPLEX,MPLEX,APLEX
1001 BPLEX
1002 APLEX,DPLEX



I want to split this string and then generate a view which holds each token separately in a different row. i.e I want to have only one system per row.

View : V_MyOrder


OrderNo SystemName
1000 UPLEX
1000 DPLEX
1000 MPLEX
1000 APLEX
1001 BPLEX
1002 APLEX
1002 DPLEX

No procedures/functions please.

I would like to know what are all the systems associated with an order, one at a time, so that I can do a join with some other systems table that I have in my database to get more information.

regards
Sriram
Steve
No functions or procedures?

Is this comma delimited data coming from an external file? CSV or TXT or something?

If so, use externally organized tables.

http://www.dba-oracle.com/art_ext_tabs.htm
SteveC
So it is safe to assume the model is:

"Let's build/design something really dumb, and then figure out a clever way to report on what we actually need?"
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.