Solved

MySQL - normalize table

Posted on 2011-09-10
2
342 Views
Last Modified: 2012-05-12
How can I take current table and normalize it so that it has one keyword_tag per user_id?

Thanks in advance for your feedback.


CREATE TABLE IF NOT EXISTS `keywords_tags` (
  `user_id` int(11) default NULL,
  `updated` int(1) default '0',
  `keyword_tags` text,
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `keywords_tags`
--

INSERT INTO `keywords_tags` (`user_id`, `updated`, keyword_tags`) VALUES
(10, 0, '2087'),
(11, 1, '2003 2006 2018 2099'),
(12, 1, '2102 2030'),
(13, 1, '2087 2085 2089 2099 2100 2444'),
(14, 0, NULL);
0
Comment
Question by:sabecs
2 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36518188
sabecs,

This may not have the best performance in the world, but it is a method that can be used especially if this is a one time thing to fix design issue and then you will be inserting into the normalized table moving forward.

First, grab my util.numbers table script, unless you already have a numbers utility table -- in which case, substitute util.numbers for your table name.

Code to parse keyword_tags:
SELECT user_id, updated
     , SUBSTRING(`keyword_tags`, n, LOCATE(' ', CONCAT(`keyword_tags`, ' '), n)-n) `keyword_tag`
FROM `keywords_tags`
JOIN util.numbers 
   ON SUBSTRING(CONCAT(' ', `keyword_tags`), n, 2) REGEXP '^ [0-9]$'

Open in new window


Hope that helps!
0
 

Author Closing Comment

by:sabecs
ID: 36518305
Thanks mwvisa1.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
A short film showing how OnPage and Connectwise integration works.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

914 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

14 Experts available now in Live!

Get 1:1 Help Now