How SQL handles strings separated by characters (like split)

Example One

CREATE PROCEDURE dbo. Vip_sendmails
@userids varchar (MAX),
@fromwho varchar (50),
@subject varchar (300),
@c varchar (MAX),
@split varchar (2)
As
BEGIN
while (CHARINDEX (@split, @userids) <>0)
Begin

Insert into Vipmail (fromwho,towho,subject,content)
VALUES (@fromwho, substring (@userids, 1,charindex (@split, @userids)-1), @subject, @c)

Set @userids = Stuff (@userids, 1,charindex (@split, @userids), ')
End
End
Go
Call Note: format if "Xxx,sss,fff,hhh," the last one will be added
EXEC dbo. Vip_sendmails ' Sada,qweqw,asdsadasds,iioo,kkkk,dddd,wqwqweqweq, ', ' sssss ', ' Test title ', ' content ', ', '

Example Two

Length before a semicolon minus no semicolon is the length of the array

Select Len (' 1:10:100 ')-len (replace (' 1:10:100 ', ': ', '))

Example three:

Define a function to call the function to fetch the value

CREATE function Get_strarraylength
(
@str varchar (1024),--string to be split
@split varchar (10)--Separator symbol
)
returns int
As
Begin
DECLARE @location int
DECLARE @start int
DECLARE @length int

Set @str =ltrim (RTrim (@str))
Set @location =charindex (@split, @str)
Set @length =1
While @location <>0
Begin
Set @start = @location +1
Set @location =charindex (@split, @str, @start)
Set @length = @length +1
End
Return @length
End

--invoke Example: SELECT dbo. Get_strarraylength (' 78,1,2,3 ', ', ', ')
--Return Value: 4