Split function in SQL

Source Address: http://blog.csdn.net/smile2me27/archive/2004/07/08/37107.aspx
1. Parameter definition and description
Output parameter: @ splitString varchar (8000) -- string to be Split
@ Separate varchar (10) --- Separator
Return: @ returnTable table (col_Value varchar (20) -- the length of a single character can be modified as needed

2. Function body
Create function SplitStr (@ splitString varchar (8000), @ separate varchar (10 ))
RETURNS @ returnTable table (col_Value varchar (20 ))
AS
BEGIN
Declare @ thisSplitStr varchar (20)
Declare @ thisSepIndex int
Declare @ lastSepIndex int
Set @ lastSepIndex = 0
If Right (@ splitString, len (@ separate) <> @ separate set @ splitString = @ splitString @ separate
Set @ thisSepIndex = CharIndex (@ separate, @ splitString, @ lastSepIndex)
While @ lastSepIndex <= @ thisSepIndex
Begin
Set @ thisSplitStr = SubString (@ splitString, @ lastSepIndex, @ thisSepIndex-@ lastSepIndex)
Set @ lastSepIndex = @ thisSepIndex 1
Set @ thisSepIndex = CharIndex (@ separate, @ splitString, @ lastSepIndex)
Insert into @ returnTable values (@ thisSplitStr)
End
Return
END

The following is an example:
Example: select * from SplitStr ('20170101 ',',')
Result:
Col_value
-----------------------------------------------------------------------------
123
456
789