Split oracle string with "/" into seprate values

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.
rajannagpalAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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
 
awking00Commented:
Can there be more than one '/'?
0
 
sdstuberCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
rajannagpalAuthor Commented:
Yes there can be multiple "/" in both the category and sub-category but the number should not exceed more then 5.
0
 
sdstuberCommented:
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
 
awking00Commented:
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
 
sdstuberCommented:
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
 
rajannagpalAuthor Commented:
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
 
rajannagpalAuthor Commented:
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
 
awking00Commented:
First create sequence cat_seq then run attached.
categories.txt
0
 
awking00Commented:
The create sequence subcat_seq and run attached.
subcategories.txt
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.