CREATE FUNCTION dbo.sms_fn_splitstring(@SourceString varchar(8000))
RETURNS @ReturnTable TABLE(SubColStr varchar(200))
AS
BEGIN
declare @CommaStr varchar(8000),@SplitStr varchar(8000),@SplitLen int,@Length int
select @SplitStr=@SourceString,@SplitLen=CHARINDEX(',',@SplitStr),@Length=DATALENGTH(@SplitStr)
while @Length>0
begin
if @SplitLen=0 set @SplitLen=@Length+1
set @CommaStr =@SplitStr
insert @ReturnTable (SubColStr) values(SUBSTRING(@CommaStr,0,@SplitLen))
select @SplitStr = SUBSTRING(@CommaStr,@SplitLen+1,@Length),@Length=DATALENGTH(@SplitStr),@SplitLen=CHARINDEX(',',@SplitStr)
end
RETURN
END