CODEWITHSUNDEEP
sqlsql-server
Patrick
Patrick

Reputation: 2781

Create related records based on string in SQL Server

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.

Upvotes: 1

Views: 251

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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;

Upvotes: 4

Related Questions