SQL Server several useful table-valued functions and scalar functions

Gets a comma-styled string of one of the

Like ' 1,2,4,5,6 ', the third one is 4.

CREATE function [dbo].[Get_strarraystrofindex](  @str nvarchar(Max),--the string to split  @split varchar(Ten),--Delimited Symbols  @index int --take the first few elements)returns varchar(1024x768) asbegin  Declare @location int  Declare @start int  Declare @next int  Declare @seed int  Set @str=LTrim(RTrim(@str))  Set @start=1  Set @next=1  Set @seed=Len(@split)    Set @location=charindex(@split,@str)   while @location<>0  and @index>@next  begin    Set @start=@location+@seed    Set @location=charindex(@split,@str,@start)    Set @next=@next+1  End  if @location =0 Select @location =Len(@str)+1 --There are two cases here: 1, the string does not exist in the delimiter symbol 2, there is a delimiter in the string, after jumping out of the while loop, @location is 0, then the default is a string behind a separator symbol.     return substring(@str,@start,@location-@start)EndGO

Get the number of comma-delimited strings

CREATE function [dbo].[Get_strarraylength](  @str nvarchar(Max),--the string to split  @split varchar(Ten)--Delimited Symbols)returns int asbegin  Declare @location int  Declare @start int  Declare @length int  Set @str=LTrim(RTrim(@str))  Set @location=charindex(@split,@str)  Set @length=1   while @location<>0  begin    Set @start=@location+1    Set @location=charindex(@split,@str,@start)    Set @length=@length+1  End  return @lengthEndGO

Split the string by a symbol to flip a table

CREATE  FUNCTION  [dbo].[splitstringtotable] (       @String  nvarchar(4000),--format such as: "1,2,3,4,"     @SplitChar  nvarchar(Ten)--split characters: ",")  RETURNS    @table  Table(IDvarchar( -))   as  BEGIN     DECLARE  @Index  INT     SET  @Index  =  0          IF @String <> "'        Begin            IF  Right(@String,1)<> @SplitChar                 SET @String = @String + @SplitChar            IF  Left(@String,1)= @SplitChar             SET @String = STUFF(@String,1,1,"')        End         while  CHARINDEX(@SplitChar,@String,@Index)>  0           BEGIN             INSERT  into @table(ID)VALUES(SUBSTRING(@String,@Index,CHARINDEX(@SplitChar,@String,                @Index)- @Index))               SET @index = CHARINDEX(@SplitChar,@String,@Index)+ 1 END  RETURN  ENDGO

SQL Server several useful table-valued functions and scalar functions