Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

robert693

Programmer
Jun 20, 2001
40
US
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.
 
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)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top