Solved

Create rows for exploded string

Posted on 2014-01-29
7
308 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
  • 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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 40

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query syntax 10 40
How to remove numeric and alpha from an alphanumeric field? 5 71
update joined tables 2 30
How many transactions can mysql handle? 3 21
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…

912 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now