sql server 函數

標籤：

1.Fun_Split 分割字串，形成返回表

CREATE FUNCTION [dbo].[Fun_Split](        @Items VARCHAR(MAX),    @SplitStr VARCHAR(MAX))RETURNS @SplitTable TABLE(Item VARCHAR(MAX)) ASBEGIN    DECLARE @Split_Index INT=0;    DECLARE @Split_len INT=0;    SET @Items = RTRIM(LTRIM(@Items));    SET @Split_Index = CHARINDEX(@SplitStr,@Items);    SET @Split_len=LEN(@SplitStr);    WHILE(@Split_Index>=1)    BEGIN            INSERT INTO @SplitTable VALUES(LEFT(@Items,@Split_Index-1));        SET @Items = SUBSTRING(@Items,@Split_Index + @Split_len,LEN(@Items)-@Split_Index);        SET @Split_Index = CHARINDEX(@SplitStr,@Items);    END    IF(@Items<>‘‘)        INSERT INTO @SplitTable VALUES(@Items);    RETURNEND

2.Fun_arrlen 分割字串，返回長度

CREATE FUNCTION [dbo].[Fun_arrlen](        @Str varchar(max),    @SplitStr varchar(max))RETURNS int ASbegin     Declare @i Int       Set @i = (Len(@Str) - Len(Replace(@Str,@SplitStr,‘‘)))/Len(@SplitStr) +1      Return(@i)end

3.Fun_GetStrIndex 類比數組擷取值

CREATE FUNCTION [dbo].[Fun_GetStrIndex](        @Str varchar(max),    @SplitStr varchar(max),    @index int)RETURNS varchar(max) ASbegin     declare @location int    declare @start int    declare @next int    declare @seed int    set @str=ltrim(rtrim(@str))    set @start=0    set @next=0    set @seed=len(@SplitStr)     set @location=charindex(@SplitStr,@str)    while @location<>0 and @index>@next    begin        set @start=@location+@seed        set @location=charindex(@SplitStr,@str,@start)        set @next=@next+1    end    if @location =0 select @location =len(@str)+1    return substring(@str,@start,@location-@start);end

運行結果：

sql server 函數