SQL Server splits strings to simulate arrays.

Arrays are a convenient data structure, but they are not supported in SQL server. Therefore, it is inconvenient to compile the storage process, we can connect multiple strings with specific connection characters as parameters and separate them as needed to simulate strings. The implementation method is to create a table value function, the split result is returned as follows:

-- ===================================================== ======
-- Author: Juan Anting
-- Create date: 2008-1-19
-- Description: Splits a string into a table.
-- Select * from fSpitStringToTable ('; 123; 223 m; 323 ;',';')
-- ===================================================== ======
Create FUNCTION [dbo]. [fSpitStringToTable]
(
@ List nvarchar (4000), -- split string
@ SplitChar char -- delimiter
)
RETURNS @ Result table (sub-string nvarchar (4000), order int)
AS
BEGIN
-- Declare the sub-string used to store the split and the remaining string
Declare @ ChildStr nvarchar (4000)
Set @ ChildStr =''

-- Remove the leading Separator
While charindex (@ splitchar, @ list, 0) = 1
Set @ list = right (@ list, Len (@ list)-1)
-- Remove the Separator
While right (@ list, 1) = @ splitchar
Set @ list = left (@ list, Len (@ list)-1)

Declare @ nindex int
Declare @ order int -- order
Set @ order = 1
While Len (@ list)> 0
Begin
Set @ nindex = charindex (@ splitchar, @ list, 0)
If @ nindex> 0
Begin
Set @ childstr = left (@ list, @ nIndex-1)
Set @ list = right (@ list, Len (@ list)-@ nindex)
End
Else
Begin
Set @ childstr = @ list
Set @ list =''
End
If len (@ ChildStr)> 0
Begin
Insert into @ Result (substring, sequence) values (@ ChildStr, @ Order)
Set @ Order = @ Order + 1
End
End

Return

END