Split string into row

Question

I am using MySQL.

I have a table named "class", the "class" table has three columns name , year and class_code like following:

Class table:

enter image description here

Now I would like to use the above table to create a new table named "temp", which contains class_code and value columns.

The rule is that each string value in each column field of a row of the above "class" table will be split into words, and each word will be inserted to the "temp" table as value column of a temp table record like following:

temp table:

enter image description here

I am using MySQL.

Is it possible to generate the "temp" table purely by using SQL statement and how?

That's :

CREATE TABLE temp;

ALTER TABLE temp DISABLE KEYS;

INSERT INTO ...(how to split the string value of each field in "class" table and insert to "temp" table??? )

P.S.: I used a simple equal length string as a value, but the actually case has very random length string, and the number of words in each string is random also.

Horrifying table design. If you have the option, store the TEMP and generate the CLASS. — orbfish, Nov 16 '11 at 17:30
possible duplicate of [how to do a split on a sql table column](http://stackoverflow.com/questions/12087885/how-to-do-a-split-on-a-sql-table-column) — Bulat, Aug 27 '14 at 15:43

Sai Kalyan Kumar Akshinthala · Answer 1 · 2011-11-16T12:12:55.200

0

You have to write a function for string splitting in MySql as

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

And use it to split the values and do the assigning the splitted values and inserting in to table in a SP.For more information refer to the MySQL Split String

edited Nov 16 '11 at 12:12

answered Nov 16 '11 at 12:03

Sai Kalyan Kumar Akshinthala

11,704
8
43
67

If I do not want to use "pos INT" at all, I prefer a function return all the substrings, how to refactor the above function definition? – Mellon Nov 16 '11 at 12:22
@Mellon, refer to the link I provide in the answer. – Sai Kalyan Kumar Akshinthala Nov 16 '11 at 12:33

score 0 · Answer 2 · answered Nov 16 '11 at 12:10

0

CREATE TABLE new_table
SELECT SUBSTRING(name,  1, 4) as field, class_code FROM old_table
UNION
SELECT SUBSTRING(name,  6, 4) as field, class_code FROM old_table
UNION
SELECT SUBSTRING(name, 11, 4) as field, class_code FROM old_table
UNION
SELECT SUBSTRING(year,  1, 4) as field, class_code FROM old_table
UNION
SELECT SUBSTRING(year,  6, 4) as field, class_code FROM old_table

answered Nov 16 '11 at 12:10

newtover

31,286
11
84
89

Hi, My post just use a simple equal length string as a value, but the actually case has very random length string, so , I think your answer is not that good. – Mellon Nov 16 '11 at 12:13
@mellon, you should have stated that in the question. Is the number of items after value split constant? – newtover Nov 16 '11 at 12:17
No, it is not constant either. – Mellon Nov 16 '11 at 12:18
@mellon, in this case the task is not that simple in plain SQL. I would split in PHP or Python and insert in a new table. – newtover Nov 16 '11 at 12:21

Split string into row

2 Answers2