Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

MySQL - normalize table

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
sabecs
Asked:
sabecs
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
sabecsAuthor Commented:
Thanks mwvisa1.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now