I would like to separate string delimited by semicolon into separate records

[robert693]

I have a query with a field that has an output of a string delimited by a semicolon. For example: Jimmy;Joe;Mary;John. I would like to separate each name into separate records:
Jimmy
Joe
Mary
John.

The string can be of any length. So there can be one name in one record, and there could be 10 in the next, and 5 in the next. I do not have the String_Split function available. Is there a way to do it with XML or something.

 

[Olaf Doschke]

STRING_SPLIT would be easiest, indeed, but before that, we used tally tables:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Bye, Olaf.





Olaf Doschke Software Engineering

https://www.doschke.name

 

[robert693]

If I have the following code, how can I use it to separate values in a field in my database?

declare @xml xml, @delimited nvarchar(max), @delimiter nvarchar(10)

set @delimited = N'a,b,c,d,e'
set @delimiter = N','

set @xml = N'<root><r>' + replace((SELECT @delimited FOR XML PATH('') ), @delimiter, '</r><r>') + '</r></root>'

select
t.value('.','varchar(max)') as [delimited items]
from @xml.nodes('//root/r') as a(t)