SQL Custom Function Split delimited string

F_split: Splitting a string into a data table

 Create FUNCTION [dbo]. [F_split] (@SplitString nvarchar (max),--source string @Separator nvarchar (10) = "--delimited symbol, default to space) RETURNS @SplitStringsTable table-Output datasheet 
 ([id] int identity (), [value] nvarchar (max)) as BEGIN DECL     is @CurrentIndex int;     DECLARE @NextIndex int;     DECLARE @ReturnText nvarchar (max);     SELECT @CurrentIndex = 1; while (@CurrentIndex <=len (@SplitString)) BEGIN SELECT @NextIndex =charindex (@Separator, @SplitString, @             CURRENTINDEX);                 IF (@NextIndex =0 OR @NextIndex is NULL) SELECT @NextIndex =len (@SplitString) +1;                 SELECT @ReturnText =substring (@SplitString, @CurrentIndex, @[email protected]);                 INSERT into @SplitStringsTable ([value]) VALUES (@ReturnText);             SELECT @[email protected]+1; END RETURN; END--Use the example SELECT * FROM dbo. F_split (' 111,b2222,323232,32d,e,323232f,g3222 ', ', ') 

Result is

ID value

-------- ---------------------------------------

1 111

2 b2222

3 323232

4 32d

5 E

6 323232f

7 g3222

=========================================================================

Second, F_splitlength: Gets the length of the segmented character array

Create function [dbo]. [F_splitlength] (  @String nvarchar (max),  --the string to be split  @Split nvarchar ()-  -separator symbol) returns INT as begin  DECLARE @ Location int  declare @start int  declare @length int    set @String =ltrim (RTrim (@String))  Set @location = CHARINDEX (@split, @String)  set @length =1 while  @location <>0  begin    Set @[email protected]+ 1    set @location =charindex (@split, @String, @start)    set @[email protected]+1  end  return @length end--invokes the example select dbo. F_splitlength (' 111,b2222,323232,32d,e,323232f,g3222 ', ', ')

The result is 7.

=========================================================================

F_splitofindex: Gets the string of a specific index after the split

Create function [dbo]. [F_splitofindex] (  @String nvarchar (max),  --the string to be split  @split nvarchar (ten),  --delimited  @index INT--Take the first few elements) returns nvarchar (1024x768) as begin  declare @location int  declare @start int  declare @next int  declare @seed int Set    @String =ltrim (RTrim (@String))  set @start =1  set @next =1  set @seed =len (@split)     SET @ Location=charindex (@split, @String) while    @location <>0 and @index > @next  begin    Set @[email Protected][email protected]    set @location =charindex (@split, @String, @start)    set @[email protected]+1   End  If @location =0 select @location =len (@String) +1     return substring (@String, @start, @[email Protected]) end--Use the example select dbo. F_splitofindex (' 111,b2222,323232,32d,e,323232f,g3222 ', ', ', 3)

SQL Custom Function Split delimited string