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

执行结果: