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

 1 --select    [dbo].[Fun_GetMaxNum]('棉 20%,麻 190%,涤纶60%')  
 2 CREATE     FUNCTION [dbo].[Fun_GetMaxNum] ( @StrAll NVARCHAR(MAX)) 
 3 RETURNS int   
 4 AS   
 5 BEGIN   --棉 50%,麻 40%,涤纶60%
 6      DECLARE @currentStr NVARCHAR(512) , --当前的字符串
 7      @backStr NVARCHAR(512), @max INT, @index INT ,@flag INT  --只准一次
 8      SET  @index=0
 9      SET  @max=0
10      SET  @flag=1
11      WHILE  ( CHARINDEX (',',@StrAll)>=0  and  @flag=1 ) --如果字符串中没有逗号, 使用 PATINDEX 函数, 支持正则
12         BEGIN  
13             IF  (CHARINDEX(',',@StrAll) = 0 ) BEGIN   SET  @flag=0 END  
14             IF  (CHARINDEX (',',@StrAll) > 0) 
15                 BEGIN  
16                     SET  @index=CHARINDEX (',',@StrAll) 
17                 END  
18             ELSE  
19                 BEGIN   
20                     SET  @index=0 
21                 END  
22             SET  @currentStr= CASE  @index WHEN  0 THEN  @StrAll ELSE  (LEFT(@StrAll ,@index-1)) END  
23             SET  @StrAll = (SELECT  SUBSTRING(@StrAll,CHARINDEX (',',@StrAll) +1,LEN (@StrAll)))
24              BEGIN  
25                 WHILE PATINDEX('%[^0-9]%',@currentStr)>0   
26                     BEGIN   
27                         SET @currentStr=STUFF(@currentStr,PATINDEX('%[^0-9]%',@currentStr),1,'') --删掉非数字的字符
28                     END 
29                 IF  CAST(@currentStr AS  INT  ) > @max 
30                     BEGIN  
31                         SET     @max = cast(@currentStr AS  INT ) 
32                     END   
33             END  
34         END  
35     RETURN @Max 
36 END
执行结果: