Solved

Create rows for exploded string

Posted on 2014-01-29
7
344 Views
Last Modified: 2014-02-18
Say I have this in Table 1
Category 1/Category 2/Category 3

Would it be possible without getting too convoluted to take that string and look at is if it was
Category 1
Category 1/Category 2
Category 1/Category 2/Category 3

Grab the ID's for those 3 entries from Table 2, and insert into Table 3 using the ID from Table 1 and the ID from Table 2
0
Comment
Question by:Gary
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39819360
It looks possible to do it like this:
CREATE TABLE IF NOT EXISTS `test_cat` (
  `category` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `test_cat`
--

INSERT INTO `test_cat` (`category`) VALUES
('Category 1/Category 2/Category 3'),
('Category A/Category B/Category C');

SELECT category, substring_index(category, '/', 1) as desired_value FROM `test_cat` 
union
SELECT category, substring_index(category, '/', 2) FROM `test_cat` 
union
SELECT category, category FROM `test_cat` 

Open in new window


Result:
category                                 desired_values
Category 1/Category 2/Category 3	Category 1
Category A/Category B/Category C	Category A
Category 1/Category 2/Category 3	Category 1/Category 2
Category A/Category B/Category C	Category A/Category B
Category 1/Category 2/Category 3	Category 1/Category 2/Category 3
Category A/Category B/Category C	Category A/Category B/Category C

Open in new window

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39819365
Note also that "union" will automatically remove duplicate values from the results. This might be a good thing, but if not you can use "union all"
0
 
LVL 58

Author Comment

by:Gary
ID: 39819497
It's a bit more complicated than that
I've built a fiddle here
http://sqlfiddle.com/#!2/88a0db

What should happen is I get three entries in the product_categories table like so
product_category_id     category_id
1                                         1
2                                         2
3                                         3
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:Terry Woods
ID: 39819613
I'm still somewhat unclear on whether your test data is similar to your real data. For example, if you have the category_name value 'Category 1/Category 2/Category 3' then do the category_id's 1, 2 and 3 get extracted from the end of each category in the category_name? Or are they sequentially generated, or something else?
0
 
LVL 58

Author Comment

by:Gary
ID: 39820915
Example is wrong it should end up with the following in product_categories
product_id     category_id
1                           1
1                           2
1                           3

Single product with ID's of the corresponding three category entries in the category table - category1, category1/category2 and category1/category2/category3


After my initial this would be great thought I realised I need to do other things with the categories that just wouldn't be possible in an SQL statement.
But in case you have any ideas I'll leave it open a while longer
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39830342
give a try.
INSERT INTO product_categories
SELECT p.product_id,c.category_id
  FROM categories c
  JOIN products p
    ON p.category LIKE CONCAT('%',c.category_name,'%');
SELECT * FROM product_categories;

Open in new window

http://sqlfiddle.com/#!2/49dcf/1
0
 
LVL 58

Author Closing Comment

by:Gary
ID: 39869166
Not sure what is happening here but seems to be working.
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 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