CODEWITHSUNDEEP
sqlsql-server
Arkadius
Arkadius

Reputation: 5

Split record in SQL Server with ID

I have such a problem. I need to separate sentences from a column of words as in tables. How to do it?

--I have table #Keywords like this:
CREATE TABLE #Keywords 
(
    Word nvarchar(400),
    Id int
)

INSERT INTO #Keywords VALUES ('some text1 to spliting', 1);
INSERT INTO #Keywords VALUES ('some text2 to spliting', 2);
INSERT INTO #Keywords VALUES ('some text3 to spliting', 3);

SELECT * FROM #Keywords

-- In result I want table like this:
CREATE TABLE #KeywordsResult 
(
    Word nvarchar(400),
    Id int
)

INSERT INTO #KeywordsResult VALUES ('some', 1);
INSERT INTO #KeywordsResult VALUES ('text1', 1);
INSERT INTO #KeywordsResult VALUES ('to', 1);
INSERT INTO #KeywordsResult VALUES ('spliting', 1);
INSERT INTO #KeywordsResult VALUES ('some', 2);
INSERT INTO #KeywordsResult VALUES ('text2', 2);
INSERT INTO #KeywordsResult VALUES ('to', 2);
INSERT INTO #KeywordsResult VALUES ('spliting', 2);
INSERT INTO #KeywordsResult VALUES ('some', 3);
INSERT INTO #KeywordsResult VALUES ('text3', 3);
INSERT INTO #KeywordsResult VALUES ('to', 3);
INSERT INTO #KeywordsResult VALUES ('spliting', 3);

SELECT * FROM #KeywordsResult

Upvotes: 0

Views: 301

Answers (1)

mauridb
mauridb

Reputation: 1569

With string_split is super-easy:

select * from [#Keywords] k
cross apply string_split(k.[Word], ' ')

Upvotes: 6

Related Questions