Split a single row from column in into as many rows as words are in the string

Question

I have a column with some Events names.I need to split one of the names into as many rows as words are in that name. Lets say 'The UK declares war on Germany' have to become

1.The
2.UK
3.declares
4.war
5.on
6.Germany

enter image description here

I believe in MSSQL there is no function for this; youd have to write your own using TSQL. Check out this similar question: https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns — Josh, Jun 27 '22 at 16:03
For splitting into *rows* there's `STRING_SPLIT` (from 2016 onwards). — Jeroen Mostert, Jun 27 '22 at 16:08
I've done it with STRING_SPLIT, but i was wondering if there is any other way.SELECT * FROM STRING_SPLIT('World War 1 began', ' ') . Like with WHERE ID = 3 or so, and it returns the name in ID 3 as separate words in separate rows — Alex Andonov, Jun 27 '22 at 16:09
Supplying sample input and output would help. It sounds like you want some kind of subquery or perhaps `CROSS APPLY`, but without more details on what your rows actually look like and what you actually want it's impossible to tell. — Jeroen Mostert, Jun 27 '22 at 16:14
Though if the OP needs those row numbers, then `STRING_SPLIT` is no use to them unless they are on Azure SQL Database. You'd have to use a JSON splitter, Tally Splitter or a CLR function on SQL Server on Premises until SQL Server 2022 is released. — Thom A, Jun 27 '22 at 16:15
I've added a picture of the column along side what the task is.Hope its visable.Sorry I am new here :/ — Alex Andonov, Jun 27 '22 at 16:19
[Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) — Thom A, Jun 27 '22 at 16:23
[`String_Split`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15#remarks) doesn't guarantee the order of the results, but you haven't stated that that is a requirement. For strings that contain punctuation marks, e.g. `Last edition of "Top of the Pops"`, what is the expected result? — HABO, Jun 27 '22 at 16:44

nbk · Accepted Answer · 2022-06-27T18:59:10.747

If you want to to use STRING_SPLIT wit a column from anoter table, you need some more code for it

CREATE TABLE test_table(id int, name varchar(100))
GO

INSERT INTO test_table VALUEs (1,'Test text'), (3,'The UK declares war on Germany')
GO

SELECT a.*
FROM test_table  as t
    CROSS APPLY STRING_SPLIT(name, ' ') AS a
 WHERE t.id = 3;  
GO

| value    |
| :------- |
| The      |
| UK       |
| declares |
| war      |
| on       |
| Germany  |

db<>fiddle here

Split a single row from column in into as many rows as words are in the string

1 Answers1