get array length after splitting string in sql server

Question

I would like to be able to split a string in SQL Server . I have a sample string

1012,1012,1012,1012,1012,1012,1012,1012

Here need length after splitting this string.

Expected output: 8

Answer 1

If you only need the number of items in a delimited string, you don't need to split it at all - here is how to do it:
You subtract the length of the string after removing all the delimiters from the length of the original string. This gives you the number of delimiters in the string. Then all you have to do is add one, since you have one more item then delimiters:

DECLARE @String varchar(50) = '1012,1012,1012,1012,1012,1012,1012,1012'

SELECT LEN(@String) - LEN(REPLACE(@String, ',', '')) + 1