Create  FUNCTION [dbo].[SplitIndex]

(

    @str AS VARCHAR(max),

    @Index AS INT,

    @SplitChar AS VARCHAR(10) = '/'

)

RETURNS VARCHAR(50)

AS

BEGIN

     DECLARE @ReturnStr   varchar(50)   --待分拆的字符串

     DECLARE @tab TABLE(id int IDENTITY(1,1), col varchar(max)) --建立临时表保存分割后的字符

     DECLARE @splitlen INT

     SET @splitlen = LEN(@SplitChar+'a')-2

     WHILE CHARINDEX(@SplitChar,@str)>0

     BEGIN

        INSERT @tab VALUES(LEFT(@str,CHARINDEX(@SplitChar,@str)-1))

        SET @str = STUFF(@str,1,CHARINDEX(@SplitChar,@str)+@splitlen,'')

     END

     INSERT @tab VALUES(@str)

     SELECT @ReturnStr = col FROM @tab WHERE id = @Index

     IF (@ReturnStr IS NULL)

     BEGIN

         SET @ReturnStr = ''

     END

     RETURN @ReturnStr

END

SELECT [dbo].[SplitIndex]('1.5oz/1.0oz',1,'/')

SELECT [dbo].[SplitIndex]('1.5oz/1.0oz',2,'/')