Function used to split strings in SQL Server

Create   Function Split (
@ Str_in Varchar ( 8000 ),
@ Separator   Varchar ( 4 ))
Returns   @ Strtable   Table (Strval Varchar ( 8000 ))
As
Begin

Declare
 @ Occurrences Int,
 @ Counter Int,
 @ Tmpstr Varchar(8000)

Set   @ Counter   =   0
If   Substring ( @ Str_in , Len ( @ Str_in ), 1 ) <>   @ Separator  
Set   @ Str_in   =   @ Str_in   +   @ Separator

Set   @ Occurrences   = ( Datalength ( Replace ( @ Str_in , @ Separator , @ Separator + ' # ' )) - Datalength ( @ Str_in )) /   Datalength ( @ Separator )
  Set   @ Tmpstr   =   @ Str_in

While   @ Counter   <=   @ Occurrences  
  Begin
Set   @ Counter   =   @ Counter   +   1
Insert   Into   @ Strtable
Values ( Substring ( @ Tmpstr , 1 , Charindex ( @ Separator , @ Tmpstr ) - 1 ))

Set   @ Tmpstr   =   Substring ( @ Tmpstr , Charindex ( @ Separator , @ Tmpstr ) + 1 , 8000 )

If   Datalength ( @ Tmpstr ) =   0
Break

  End
  Return  
End

Input string return table.
Call
Select DBO. Split ('string to be separated ', 'separator ')