How to extract data separated by a semicolon

Question

Lets say I have a table in SQLServer named Tasks. It contains some tasks with people assigned to tasks. Workers are in one column. They are separated by semicolon.

ID  Workers
1   John Newman; Troy Batchelor; Mike Smith
2   Chris Portman
3   Sara Oldman; Greg House

I need to separate workers from column like below

The result:

ID  Worker
1   John Newman
1   Troy Batchelor
1   Mike Smith
2   Chris Portman
3   Sara Oldman
3   Greg House

I have no idea what to do. Do I have to use some procedure or simple query is enough?

Possible duplicate of [How do I split a string so I can access item x?](https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) — Denis Rubashkin, Jul 11 '19 at 07:24
Always search the internet first, before posting a question. String splitting is one of the most common questions. The better answer is to fix your schema and properly normalize your tables. — SMor, Jul 11 '19 at 12:11

score 3 · Answer 1 · answered Jul 11 '19 at 08:37

i solved your problem without using any function or stored procedure

SELECT ID,Workers FROM tblSemiColon


SELECT ID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Workers
FROM
(
SELECT ID,CAST('<XMLRoot><RowData>' + REPLACE(Workers,';','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   tblSemiColon
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

this will work across all the version of sql server..i had tested it....

you can reduce the size of varchar length..

score 1 · Answer 2 · answered Jul 11 '19 at 07:22

1

If you are on sql-server 2016 or higher you can use STRING_SPLIT function

SELECT id,  value  
FROM Tasks
    CROSS APPLY STRING_SPLIT(Workers, ';')

answered Jul 11 '19 at 07:22

Shikhar Arora

886
1
9
14

How to extract data separated by a semicolon

2 Answers2