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
执行结果:
