sql 拆分函数，相当于程序里面得数组

---------------------------可以拆分成多少个---------------

create FUNCTION [dbo].[Get_StrArrayLength]

( @str varchar(5000) ,  --要分割的字符串

  @split varchar(10)  --分隔符号

  )

RETURNS int

AS

BEGIN

    DECLARE @location INT

    DECLARE @start INT

    DECLARE @length INT

    SET @str = LTRIM(RTRIM(@str))

    SET @location = CHARINDEX(@split, @str)

    SET @length = 1

    WHILE @location <> 0

        BEGIN

            SET @start = @location + 1

            SET @location = CHARINDEX(@split, @str, @start)

            SET @length = @length + 1

        END

    RETURN @length

END

---------------------------根据 拆分个数，获取 第几个 ---------------

SET QUOTED_IDENTIFIER ON

GO

create FUNCTION [dbo].[Get_StrArrayStrOfIndex]

( @str varchar(5000) ,  --要分割的字符串

  @split varchar(10) ,  --分隔符号

  @index int --取第几个元素

  )

RETURNS varchar(5000)

AS

BEGIN

    DECLARE @location INT

    DECLARE @start INT

    DECLARE @next INT

    DECLARE @seed INT

    SET @str = LTRIM(RTRIM(@str))

    SET @start = 1

    SET @next = 1

    SET @seed = LEN(@split)

    SET @location = CHARINDEX(@split, @str)

    WHILE @location <> 0

        AND @index > @next

        BEGIN

            SET @start = @location + @seed

            SET @location = CHARINDEX(@split, @str, @start)

            SET @next = @next + 1

        END

    IF @location = 0

        SELECT  @location = LEN(@str) + 1

    RETURN SUBSTRING(@str,@start,@location-@start)

END

---------------------------------------- 利用上面的 两个函数，进计算 

ALTER FUNCTION GetApplyScore( @Str varchar(8000), @FGF varchar(50))

RETURNS decimal(18,2)

AS

BEGIN

declare @StrLen int ,@XH int,@RetScore decimal(18,2),@ParamStr varchar(500),@ParamStrScore decimal(18,2)

select @StrLen=dbo.Get_StrArrayLength(@Str,@FGF) ,@XH=0,@RetScore=0

if( @StrLen>0)

begin 

  while @XH<@StrLen

begin

set @XH=@XH+1

select @ParamStr=dbo.Get_StrArrayStrOfIndex(@Str,@FGF,@XH)

select @ParamStrScore=dbo.Get_StrArrayStrOfIndex(REPLACE(@ParamStr,' ','|'),'|',3)

    set @RetScore=@RetScore+ISNULL(@ParamStrScore,0)

end

end  

return @RetScore

END

GO