如何将字符串字段句子拆分为单词，然后将它们插入具有相同键ID的新表中? [英] How can I split up a string field sentence into words and insert them into a new table with the same key id?

问题描述

我有一个名为Pads的表格，其中有一个名为关键字的字段，它有一个单词列表，也称为一个句子.

I have a table called Pads with a field called keywords, it has a list of words aka a sentence.

例如

Pad
ID=1 Keywords=red brown green
ID=2 keywords=green orange blue

关键字"字段中可以包含40个单词.

The keywords field could have 40 words in it.

我想创建一个新的表格关键字

I want to create a new table keywords

例如

Keywords
ID=1 word=red
ID=1 word=brown
ID=1 word=green
ID=2 word=green
ID=2 word=orange
ID=2 word=blue

有人可以指向我一些sql来创建数据或将数据插入到这个新表中吗?

Can someone point me at some sql to create / insert data into this new table?

编辑-回复斯皮尼·诺曼，我的字段和空格略有不同，但不包含逗号，但是，这就是我所替换的所有内容，并且出现错误，我不知道为什么?...

EDIT - Reply to Spinny Norman, I've got slightly different fields and spaces not commas, however thats all I replaced and I'm getting an error and I can't see why ?...

推荐答案

这里有很多方法:

There's quite a lot of ways here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html, but they all require you to name the index of the item as well. So, if you know that there is a max number of keywords per item, you could use one of these. An example, for 3 keywords per item:

insert into keywords (id, word) (
    select id, replace(substring(substring_index(Keywords, ',', 1), length(substring_index(Keywords, ',', 1 - 1)) + 1), ',', '') as item1
    from Pads
union all
    select id, replace(substring(substring_index(Keywords, ',', 2), length(substring_index(Keywords, ',', 2 - 1)) + 1), ',', '') as item2
    from Pads
union all
    select id, replace(substring(substring_index(Keywords, ',', 3), length(substring_index(Keywords, ',', 3 - 1)) + 1), ',', '') as item3
    from Pads
);

您还可以过滤出空值，以便能够使用最多3个(在这种情况下)关键字".

You could also filter out null values to be able to use "up to 3 (in this case) keywords".

如果仅执行一次，则不必使用并集(而且，如果您这样做，显然应该用select包围整个并集).因此，请改用此:

if you're doing this just once, you don't have to use the unions (and also, you should apparently surround the whole union with a select if you do). So, use this instead:

insert into words (padid, word)
  select padid, replace(substring(substring_index(English45, ' ', 1), 
   length(substring_index(English45, ' ', 1 - 1)) + 1), ' ', '') as item1
   from Pads
   having item1 <> '';

重复2、3等，直到不再插入.

And repeat for 2, 3 etc until no inserts are made any more.

这篇关于如何将字符串字段句子拆分为单词，然后将它们插入具有相同键ID的新表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！