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)
The result is 323232.
Transferred from: http://www.cnblogs.com/xiaofengfeng/archive/2012/06/01/2530930.html
-------------------------------------------------------------------------------------
SQL string Comma separated function
Following sql-function the result of creating the function database output is separated by commas, and many parameters are passed in the development with the comma Note string parameter (not recommended when the data is large)
Example: Find the name "Zhang San, li er" data at this time in the database to do this parameter processing
The function code is as follows
CREATE FUNCTION [dbo]. [Fnsplitstr] (
@sText NVARCHAR (Max),
@sDelim CHAR (1)
)
RETURNS @retArray TABLE (
Value VARCHAR (100)
)
As
BEGIN
DECLARE
@posStart BIGINT,
@posNext BIGINT,
@valLen BIGINT,
@sValue NVARCHAR (100);
IF @sDelim is NULL
BEGIN
IF LEN (@sText) >100 SET @sText = SUBSTRING (@sText, 1, 100)
INSERT @retArray (value)
VALUES (@sText);
END
ELSE
BEGIN
SET @posStart = 1;
While @posStart <= LEN (@sText)
BEGIN
SET @posNext = CHARINDEX (@sDelim, @sText, @posStart);
IF @posNext <= 0
SET @valLen = LEN (@sText)-@posStart + 1;
ELSE
SET @valLen = @posNext-@posStart;
SET @sValue = SUBSTRING (@sText, @posStart, @valLen);
SET @posStart = @posStart + @valLen + 1;
IF LEN (@sValue) > 0
BEGIN
IF LEN (@sValue) >100 SET @sValue = SUBSTRING (@sValue, 1, 100)
INSERT @retArray (value)
VALUES (@sValue);
END
END
END
RETURN
END
SQL Custom Function split delimited string