SQL: breaks down strings Based on the given delimiter

If you have a string such as "Sun, Star, moon, clouds", you want to use the given separator ', 'How can we break this string into [Sun] [Star] [moon] [clouds] elements? Create a function with the following code:

Create Function [DBO]. [split_strbydelimiter] (@ string varchar (8000), @ delimiter char (1 ))
Returns @ temptable table (items varchar (8000 ))
As
Begin
Declare @ idx int
Declare @ slice varchar (8000)
 
Select @ idx = 1
If Len (@ string) <1 or @ string is null return
 
While @ idx! = 0
Begin
Set @ idx = charindex (@ delimiter, @ string)
If @ idx! = 0
Set @ slice = left (@ string, @ idx-1)
Else
Set @ slice = @ string
 
If (LEN (@ slice)> 0)
Insert into @ temptable (items) values (@ slice)
 
Set @ string = right (@ string, Len (@ string)-@ idx)
If Len (@ string) = 0 break
End
Return
End

Example: If you enter

Select * From DBO. split_strbydelimiter ('Sun, Star, moon, clouds ',',')

Result returned

Sun

Star

Moon

Clouds

 

In the code above, how many elements are returned?

Create Function [DBO]. [getcount_split_strbydelimiter] (@ string varchar (8000), @ delimiter char (1 ))
Returns int
As
Begin
Declare @ temptable table (items varchar (8000 ))
Declare @ splitcount int
Declare @ idx int
Declare @ slice varchar (8000)
 
Select @ idx = 1
If Len (@ string) <1 or @ string is null return 0
 
While @ idx! = 0
Begin
Set @ idx = charindex (@ delimiter, @ string)
If @ idx! = 0
Set @ slice = left (@ string, @ idx-1)
Else
Set @ slice = @ string
 
If (LEN (@ slice)> 0)
Insert into @ temptable (items) values (@ slice)
 
Set @ string = right (@ string, Len (@ string)-@ idx)
If Len (@ string) = 0 break
End
Set @ splitcount = (select count (*) from @ temptable)
Return @ splitcount
End

Example

Select DBO. getcount_split_strbydelimiter ('Sun, Star, moon, clouds ',',')

Result returned

4