Copy Code code as follows:
--Create a function with two parameters in the function
Create FUNCTION [dbo]. [Splittotable]
(
@SplitString nvarchar (max),--the string entered
@Separator nvarchar (10) = ' '--segmentation condition
)
--The returned data is a table
RETURNS @SplitStringsTable TABLE
(
[id] int identity (1,1),
[value] nvarchar (max)
)
As
BEGIN
DECLARE @CurrentIndex int;--Current Index
DECLARE @NextIndex int;--Next Index
DECLARE @ReturnText nvarchar (max);--Return content
SELECT @CurrentIndex =1;--Set the current index to an initial value of 1
--Executes the loop body when the current index is less than the string length
while (@CurrentIndex <=len (@SplitString))
BEGIN
The--charindex function returns the starting position of a character or string in another string. The CHARINDEX function calls the following methods:
--charindex (expression1, expression2 [, Start_location])
-Expression1 is the character to look for in the expression2, start_location is the CHARINDEX function begins to find expression2 in the expression1 position.
SELECT @NextIndex =charindex (@Separator, @SplitString, @CurrentIndex);
IF (@NextIndex =0 OR @NextIndex is NULL)
SELECT @NextIndex =len (@SplitString) +1;
--Functional usage: SUBSTRING (expression, start, length)
SELECT @ReturnText =substring (@SplitString, @CurrentIndex, @NextIndex-@CurrentIndex);
INSERT into @SplitStringsTable ([value]) VALUES (@ReturnText);
SELECT @CurrentIndex = @NextIndex +1;
End
return;
End
Go
--Test function
SELECT * FROM dbo. Splittotable (' a,b,c,d,e,f,g ', ', ', ')
