Copy codeThe Code is as follows:
-- Create a function with two parameters
Create FUNCTION [dbo]. [SplitToTable]
(
@ SplitString nvarchar (max), -- input string
@ 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 initial value of the current index to 1
-- Execute the loop body when the current index is smaller 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 is called as follows:
-- CHARINDEX (expression1, expression2 [, start_location])
-- Expression1 is the character to be searched in expression2, and start_location is the position where the CHARINDEX function starts to find expression1 in expression2.
SELECT @ NextIndex = charindex (@ Separator, @ SplitString, @ CurrentIndex );
IF (@ NextIndex = 0 OR @ NextIndex is null)
SELECT @ NextIndex = len (@ SplitString) + 1;
-- Function 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 functions
Select * FROm dbo. SplitToTable ('a, B, c, d, e, f, G ',',')