将字符串拆分成行 [英] Split string into row
问题描述
我使用MySQL。
我有一个名为 class 的表, class 表有三列 name
, year
和 class_code
:
类表:
现在我想使用上表创建一个新表名为 temp ,其中包含 class_code
和 value
列。
规则是,上述类表格的每一列字段中的每个字符串值都将拆分为单词,每个字将插入到 temp 表中作为 temp 表记录的 value
列,如下所示: / p>
temp 表格:
我使用MySQL。
是否可以通过使用SQL语句以及如何生成 temp 表格?
这是:
CREATE TABLE temp;
ALTER TABLE temp DISABLE KEYS;
INSERT INTO ...(如何拆分类表中每个字段的字符串值并插入到temp表中)
PS:
我使用一个简单的等长字符串作为值, >随机长度字符串,每个字符串中的单词数量随机。解决方案您必须为MySql中的字符串拆分编写函数
CREATE FUNCTION SPLIT_STR(
x VARCHAR ),
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,'');
并使用它来分割值,并分配分割值并插入到SP.有关详情,请参阅 MySQL分割字符串
I am using MySQL.
I have a table named "class", the "class" table has three columns
name
,year
andclass_code
like following:Class table:
Now I would like to use the above table to create a new table named "temp", which contains
class_code
andvalue
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:
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.
解决方案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
这篇关于将字符串拆分成行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!