20181123_SQL Server 2008_找出以逗号分隔的字符串中最大的数字

--select    [dbo].[Fun_GetMaxNum]('棉 20%,麻 190%,涤纶60%')  
CREATE     FUNCTION [dbo].[Fun_GetMaxNum] ( @StrAll NVARCHAR(MAX)) 
RETURNS int   
AS   
BEGIN   --棉 50%,麻 40%,涤纶60%
     DECLARE @currentStr NVARCHAR(512) , --当前的字符串
     @backStr NVARCHAR(512), @max INT, @index INT ,@flag INT  --只准一次
     SET  @index=0
     SET  @max=0
     SET  @flag=1
     WHILE  ( CHARINDEX (',',@StrAll)>=0  and  @flag=1 ) --如果字符串中没有逗号, 使用 PATINDEX 函数, 支持正则
        BEGIN  
            IF  (CHARINDEX(',',@StrAll) = 0 ) BEGIN   SET  @flag=0 END  
            IF  (CHARINDEX (',',@StrAll) > 0) 
                BEGIN  
                    SET  @index=CHARINDEX (',',@StrAll) 
                END  
            ELSE  
                BEGIN   
                    SET  @index=0 
                END  
            SET  @currentStr= CASE  @index WHEN  0 THEN  @StrAll ELSE  (LEFT(@StrAll ,@index-1)) END  
            SET  @StrAll = (SELECT  SUBSTRING(@StrAll,CHARINDEX (',',@StrAll) +1,LEN (@StrAll)))
             BEGIN  
                WHILE PATINDEX('%[^0-9]%',@currentStr)>0   
                    BEGIN   
                        SET @currentStr=STUFF(@currentStr,PATINDEX('%[^0-9]%',@currentStr),1,'') --删掉非数字的字符
                    END 
                IF  CAST(@currentStr AS  INT  ) > @max 
                    BEGIN  
                        SET     @max = cast(@currentStr AS  INT ) 
                    END   
            END  
        END  
    RETURN @Max 
END

执行结果: