in SQL Server

Split Function for T-SQL using XML

Here is some sample code to split a string and return the results via a table.

CREATE FUNCTION [dbo].[split]
(
@del char(1),
@str varchar(max)
)
RETURNS @tResult TABLE
(
val varchar(max)
)
AS
BEGIN
declare @xml xml
set @xml = N'<root><r>’ + replace(@str,@del,'</r><r>’) + ‘</r></root>’

insert into @tResult(val)
select
r.value(‘.’,’varchar(max)’) as item
from @xml.nodes(‘//root/r’) as records(r)

RETURN
END