Solved

Create rows for exploded string

Posted on 2014-01-29
7
338 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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…

738 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