Split a database field delimited by a semicolon

[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)

 

[jbenson001]

You can create a user defined function and return what you need.

We do something similar however, we do not use FOR XML. We simply pass in a delimited string and the delimiter and pass back a table of values. Then in the SQL we join to the returned table

 

[Olaf Doschke]

Earlier I pointed you to the fine CSV splitter function by Jeff Moden. If you didn't understand, why didn't you respond?

Are you not able to add a stored proc and look for a solution you can split strings with an ad-hoc script you can execute? I'd not recommend that. I don't see a way to convert a field to XML and at the same time getting access to its .nodes() function to execute an XPath query, this would at least need an intermediate step of CTE or a temp table and is overcomplicating things in comparison to a CSV splitter function.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name