Split string values in sql server and insert into DATABASE

There are tow way to do this-
1- Using CHARINDEX 
2- Using PATINDEX 

1- Using CHARINDEX 

declare @string varchar(500)

SET @string = '1,2,3,4,5,'

declare @pos numeric(20)

declare @piece varchar(50)

SET @pos = charindex(',' , @string)

while @pos <> 0

begin

SET @piece = LEFT(@string, @pos-1)

print @piece

SET @string = stuff(@string, 1, @pos, NULL)

SET @pos = charindex(',' , @string)

end

print @string  --this is required to print last string

Output:
2- Using PATINDEX 
declare @string varchar(500)

1

2

3

4

5

SET @string = 'abc-xyx-pqr-ttt-mmm-'

declare @pos numeric(20)

declare @piece varchar(50)

SET @pos = patindex('%,%' , @string)

while @pos <> 0

begin

SET @piece = LEFT(@string, @pos-1)

print @piece

SET @string = stuff(@string, 1, @pos, NULL)

SET @pos = charindex(',' , @string)

end

print @string  --this is required to print last string

Output:

abc

xyx

pqr

ttt

mmm

 Insert Into datatable-

declare @string varchar(500)
SET @string = 'bb,tt,er,1,2,3,4,5,6,7,8,'
declare @pos int
declare @piece int

SET @pos = charindex(',' , @string)
while @pos <> 0
begin
SET @piece = LEFT(@string, @pos-1)
print @piece
SET @string = stuff(@string, 1, @pos, NULL)
SET @pos = charindex(',' , @string)
insert into test1(code)values(@piece)

end

Output: