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 (Ten)=' '--separator Symbol, default is a space) RETURNS @SplitStringsTable TABLE--Output data table ([ID]intIdentity1,1), [value] nvarchar (max)) as BEGIN DECLARE @CurrentIndexint; DECLARE @NextIndexint;     DECLARE @ReturnText nvarchar (max); SELECT @CurrentIndex=1; while (@CurrentIndex<=Len (@SplitString)) BEGIN SELECT @NextIndex=charindex (@Separator, @SplitString, @CurrentIndex); IF (@NextIndex=0OR @NextIndex is NULL) SELECT @NextIndex=len (@SplitString) +1; SELECT @ReturnText=substring (@SplitString, @CurrentIndex, @NextIndex-@CurrentIndex);                 INSERT into @SplitStringsTable ([value]) VALUES (@ReturnText); SELECT @CurrentIndex[Email protected]+1; END RETURN; END--using the exampleSelect* 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 split @Split nvarchar (Ten)  --separator symbol) returnsint  asBEGIN DECLARE @locationintDeclare @startintDECLARE @lengthint    Set@String =LTrim (RTrim (@String))Set@location =charindex (@split, @String)Set@length =1   while@location <>0beginSet@[email protected]+1    Set@location =charindex (@split, @String, @start)Set@[email protected]+1Endreturn@length End--Invoke ExampleSelectDbo. 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 split @split nvarchar (Ten),  --delimited symbol @indexint--take the first few elements) returns nvarchar (1024x768)  asBEGIN DECLARE @locationintDeclare @startintDeclare @nextintDeclare @seedint    Set@String =LTrim (RTrim (@String))Set@start =1  Set@next =1  Set@seed =Len (@split)Set@location =charindex (@split, @String) while@location <>0and @index >@next beginSet@[email protected]+@seedSet@location =charindex (@split, @String, @start)Set@[email protected]+1Endif@location =0 Select@location =len (@String) +1     returnSUBSTRING (@String, @start, @location-@start) End--using the exampleSelectDbo. F_splitofindex ('111,b2222,323232,32d,e,323232f,g3222',',',3)

The result is 323232.

Transferred from: http://www.cnblogs.com/xiaofengfeng/archive/2012/06/01/2530930.html

SQL Custom Function Split delimited string