?
Solved

Split oracle string with "/" into seprate values

Posted on 2008-11-12
11
Medium Priority
?
974 Views
Last Modified: 2013-12-19
Hi

I have following records in the table

Category                                                       sub-category
BAKED BEANS/CANNED BREAD                  BAKED BEANS
ALL OTHER BREAKFAST FOOD            INSTANT BREAKFAST
ALL OTHER SAUCES                       HORSERADISH/HORSERADISH SAUCE

I want to create two tables one with categories and other with sub-categories as follow

category table : cat-id (pk), Category
sub-category table: sub-id (pk), sub-cat, cat-id (FK)

Category table

cat-id                category
   1                     BAKED BEANS
    2                  CANNED BREAD    
  3               ALL OTHER BREAKFAST FOOD    
4                 ALL OTHER SAUCES            

 sub-category table

sub-id           sub-cat                              cat-id
1                        BAKED BEANS                   1
2                           BAKED BEANS                2
3                      INSTANT BREAKFAST          3
4                    HORSERADISH                        4
5                   HORSERADISH SAUCE             4

when there is "/" in categories it means we have to split the categories and insert as separate column value also we have to maintain the association in sub category table.
0
Comment
Question by:rajannagpal
  • 4
  • 4
  • 3
11 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 22940375
Can there be more than one '/'?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 22940520
First, create the vcarray type and str2tbl function below then use the query that follows



CREATE OR REPLACE TYPE VCARRAY AS TABLE OF VARCHAR2(4000);
 
CREATE OR REPLACE FUNCTION str2tbl(p_string IN   varchar2,
                                   p_delimiter IN varchar2:= ',')
    RETURN vcarray
    PIPELINED
AS
    v_length number := LENGTH(p_string);
    v_start number := 1;
    v_index number;
BEGIN
    WHILE (v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, p_delimiter, v_start);
 
        IF v_index = 0
        THEN
            PIPE ROW (SUBSTR(p_string, v_start));
            v_start := v_length + LENGTH(p_delimiter);
        ELSE
            PIPE ROW (SUBSTR(p_string, v_start, v_index - v_start));
            v_start := v_index + LENGTH(p_delimiter);
        END IF;
    END LOOP;
 
    RETURN;
END str2tbl;
 
 
SELECT ROWNUM, subcategory, catid
  FROM (  SELECT subcategory, catid
            FROM categories c,
                 (SELECT c2.COLUMN_VALUE category, s2.COLUMN_VALUE subcategory
                    FROM relationships r,
                         table(str2tbl(r.category, '/')) c2,
                         table(str2tbl(r.subcategory, '/')) s2) r
           WHERE c.category = r.category
        ORDER BY catid, subcategory)

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22940551
oh I'm sorry, now that I've posted I realize I misread the question.

The above will work assuming you have already created a "categories" table from your relationships starting table.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:rajannagpal
ID: 22940563
Yes there can be multiple "/" in both the category and sub-category but the number should not exceed more then 5.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22940713
fortunately, it's easy to build the categories table from the relationships table too...


SELECT ROWNUM, c.COLUMN_VALUE category
  FROM relationships r, table(str2tbl(r.category, '/')) c
0
 
LVL 32

Expert Comment

by:awking00
ID: 22942114
Can there be multiple '/' in both the category and sub-category fields for the same record? Like this
category          sub-category
milk/juice         skim/whole/orange/grapefruit

If so, how would you assign the cat_id?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22942182
ok, this is better so it will be deterministic

the catid will be assigned in order from left to right, within alphabetic of the concatenated string


select rownum catid, category from (
SELECT c.COLUMN_VALUE category
  FROM relationships r, table(str2tbl(r.category, '/')) c
order by 1)
0
 

Author Comment

by:rajannagpal
ID: 22942924
Can there be multiple '/' in both the category and sub-category fields for the same record? Like this
category          sub-category
milk/juice         skim/whole/orange/grapefruit

We have excluded this case so it will never  happen. We have only three cases as I mentioned in the example above.
0
 

Author Comment

by:rajannagpal
ID: 22943786
I think the solution you provide above :

SELECT ROWNUM, subcategory, catid
  FROM (  SELECT subcategory, catid
            FROM categories c,
                 (SELECT c2.COLUMN_VALUE category, s2.COLUMN_VALUE subcategory
                    FROM relationships r,
                         table(str2tbl(r.category, '/')) c2,
                         table(str2tbl(r.subcategory, '/')) s2) r
           WHERE c.category = r.category
        ORDER BY catid, subcategory)

should be :

SELECT distinct c2.COLUMN_VALUE category, s2.COLUMN_VALUE subcategory
                    FROM category_temp r,
                         table(str2tbl(r.category, '/')) c2,
                         table(str2tbl(r.subcategory, '/')) s2


we don't need to create separate table for categories because it has the same values as relation table.
so if we have records like below in the relational table::

category                      subCategory
cat1/cat2                         sub
cat                                   sub1/sub2

will brake down into

cat1                       sub
cat2                        sub
cat                          sub1
cat                        sub2

 Can you please provide me a good link where I can understand "PIPE ROW" and table () function.

Thanks

0
 
LVL 32

Expert Comment

by:awking00
ID: 22943928
First create sequence cat_seq then run attached.
categories.txt
0
 
LVL 32

Expert Comment

by:awking00
ID: 22943945
The create sequence subcat_seq and run attached.
subcategories.txt
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question