SELECT Fun_Split('abc,edfg,hklm',1,1)
will RETURN 'abc'
SELECT Fun_Split('abc,edfg,hklm',1,2)
will RETURN 'edfg'
SELECT Fun_Split('abc,edfg,hklm',2,3)
will RETURN 'hklm'
SELECT Fun_Split('abc,edfg,hklm',1,1)
will RETURN 'abc'
SELECT Fun_Split('abc,edfg,hklm',1,2)
will RETURN 'edfg'
SELECT Fun_Split('abc,edfg,hklm',2,3)
will RETURN 'hklm'
CREATE FUNCTION [dbo].[fun_Split](@strTarget VARCHAR(100),@iStart TINYINT,@iEnd TINYINT) RETURNS VARCHAR(100) AS BEGIN --Author: Eugene --Description: Fun_Split('abc,edfg,hklm',1,1) RETURN 'abc' --CreateDate:2008/12/12 DECLARE @iNum TINYINT; DECLARE @iLength TINYINT; DECLARE @strReturn VARCHAR(100); SET @iLength=1; SET @iNum=1; WHILE @iLength<=@iStart BEGIN SET @iNum=CHARINDEX(',',@strTarget,@iNum); SET @iLength=@iLength+1; SET @iNum=@iNum+1; END SET @iStart=1; SET @iLength=@iEnd-1 SET @iEnd=@iNum-1; IF @iLength<>0 BEGIN SET @iStart=@iNum; SET @iEnd=CHARINDEX(',',@strTarget,@iNum); IF @iEnd=0 BEGIN SET @iEnd=LEN(@strTarget)+1; END END SET @iLength=@iEnd -@iStart; SET @strReturn=SUBSTRING(@strTarget,@iStart,@iLength); RETURN @strReturn; END