Create related records based on string in SQL Server

Question

I need to create a query that takes rows from a table and inserts into a related table all the splitted strings.

Example:

In table Keywords I have the row:

Id   Name 
1    RENAULT CLIO MTV

And I need to create a query that takes the row and create 1 row for each word like this:

In the table KeywordSearches:

Id: (Identity Increment)
Name: RENAULT
Keyword_Id: 1

Id: (Identity Increment)
Name: CLIO
Keyword_Id: 1

Id: (Identity Increment)
Name: MTV
Keyword_Id: 1

I need to be able to create all the related Keyword Search based on every rows of the table Keywords.

Thanks.

Have you tried searching on the site?http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco in this case your separator will be white space. — Giorgio Minardi, Jul 13 '13 at 20:58
Hi, I'm not finding a match for what I need, because if I understand correctly, they split a string and not rows from a table to another. — Patrick, Jul 13 '13 at 21:07

Gordon Linoff · Accepted Answer · 2013-07-13T23:03:21.150

One way to get the list of keywords is to use a recursive CTE:

with keywords as (
      select 1 as id, 'RENAULT CLIO MTV' as keywords union all
      select 2 as id, 'A B' as keywords
     ),
     cte as (
      select id,
             (case when keywords like '% %'
                   then left(keywords, charindex(' ', keywords))
                   else keywords
              end) as keyword,
             (case when keywords like '% %'
                   then substring(keywords, charindex(' ', keywords)+1, 1000)
                   else ''
              end) as rest
      from keywords
      union all
      select id,
             (case when rest like '% %'
                   then left(rest, charindex(' ', rest))
                   else rest
              end) as keyword,
             (case when rest like '% %'
                   then substring(rest, charindex(' ', rest)+1, 1000)
                   else ''
              end) as rest
      from cte
      where len(rest) > 0
     )
select id, keyword
from cte;

Using the same structure, you can replace the final select with an insert:

insert into KeywordSearches(name, keyword_id)
    select keyword, id
    from CTE;

This assumes that you have set up the id as an identity column.

Here is a SQLFiddle for the first query.

EDIT:

I think the final query would be something like:

with cte as (
      select id,
             (case when keywords like '% %'
                   then left(keywords, charindex(' ', keywords))
                   else keywords
              end) as keyword,
             (case when keywords like '% %'
                   then substring(keywords, charindex(' ', keywords)+1, 1000)
                   else ''
              end) as rest
      from keywords
      union all
      select id,
             (case when rest like '% %'
                   then left(rest, charindex(' ', rest))
                   else rest
              end) as keyword,
             (case when rest like '% %'
                   then substring(rest, charindex(' ', rest)+1, 1000)
                   else ''
              end) as rest
      from cte
      where len(rest) > 0
     )
insert into KeywordSearches(name, keyword_id)
    select keyword, id
    from CTE;

Hi, thanks. I'm having dificulties to adapt your solution to the tables Keywords and KeywordSearches, can you help in this? I'm not able to find a way to apply your solution to all the rows in the Keywords table. — Patrick, Jul 13 '13 at 21:35
Just drop the definition of `keywords` in the `with` clause. I put it there just for testing purposes. — Gordon Linoff, Jul 13 '13 at 21:36

Create related records based on string in SQL Server

1 Answers1

Linked