A simple way to normalize a table

Question

I have a table like this -this is a part of users table which is getting update with more rows when a user is registering :

user_id languages 
1       english, french 
2       english,german,french
3       german
4       english, spanish

and in order to do a littel search I'm intrested of transforming this table into:

user_id language
1          english
1          french
2          english
2          german
2          french
3          german
4          english
4          spanish

Comment : not creating a new table but just right the correct select query from users table to do that. I'm using mysql with phpmyadmin. Any help will be welcome. Thank's.

Great idea to fix a bad design! Never, ever store data as comma separated items, it will only cause you lots of trouble. — jarlh, Apr 27 '17 at 11:39
Could you please edit the question and reword the last part? I can't understand what you're asking. — Álvaro González, Apr 27 '17 at 11:50
It sounds like you want a view or a chunk of query that will handle your comma separated lists in the `languages` column. You can use a bunch of nested SUBSTRING_INDEX() function calls to get this. I hesitate to tell you how to do it because it's such a terrible idea to keep this table rather than creating a new normalized one. — O. Jones, Apr 27 '17 at 11:59
It is not possible to write a query to do this. The best you can do is write a procedure which unstrings the tokens and outputs to a table. — P.Salmon, Apr 27 '17 at 12:00
Read this and weep. http://sqlfiddle.com/#!9/7c67c6/10/0 Better yet, prefix it with `CREATE TABLE language AS` then get rid of this comma-separated stuff. — O. Jones, Apr 27 '17 at 12:06
Thank's or the idea's. I have decided (as someone here stated) to do replace the languages to language1, language2 and not to store the languages by comma (I have al limitation of 5 languages). — Toto88, Apr 27 '17 at 12:23
@Toto88 That is not a good solution, either. Each language should be stored on a separate row, not as a separate column. — Jerrad, Apr 27 '17 at 12:25

score 0 · Answer 1 · edited May 23 '17 at 12:17

As others have stated, storing comma-separated values in a column will cause you all sorts of pain over time. Having said that...

Create a numbers table (one way to do that is described here). Once you have that, you can use it to split your comma-separated values onto separate rows:

select lang.user_id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(lang.languages, ', ', numbers.n), ', ', -1) language
from numbers 
inner join lang on CHAR_LENGTH(lang.languages) - CHAR_LENGTH(REPLACE(lang.languages, ', ', ' ')) >= numbers.n - 1
order by lang.user_id, numbers.n;

DEMO

A simple way to normalize a table

1 Answers1