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