You could try something similar to the following:
create function [dbo].[Split_Values]
(
@String varchar(4000)
,@Delimiter varchar(1)
)
returns @Split_Values table(String_Value varchar(200))
as
begin
insert into @Split_Values
(
String_Value
)
(
selectsubstring
(
@Delimiter + @String + @Delimiter
,N + 1
,charindex(@Delimiter,@Delimiter + @String + @Delimiter,N + 1) - N - 1
) as String_Value
fromdbo.Tally
whereN < len(@Delimiter + @String + @Delimiter)
and substring(@Delimiter + @String + @Delimiter,N,1) = @Delimiter
)
return
end
For the above function, you will need a "Tally" table...read up on that...it will be useful in many other situations as well.