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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Information Technology SpecialistCommented:
Can there be more than one '/'?
0
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
awking00Information Technology SpecialistCommented:
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
awking00Information Technology SpecialistCommented:
First create sequence cat_seq then run attached.
categories.txt
0
awking00Information Technology SpecialistCommented:
The create sequence subcat_seq and run attached.
subcategories.txt
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.