How to get the count of number of items in a column which is separated by comma

alter function like.

ALTER FUNCTION [dbo].[SplitAndCount]    
(
	@String VARCHAR(8000), @Delimiter CHAR(1)
)
RETURNS INT 
AS
BEGIN
DECLARE @temptable TABLE (items VARCHAR(8000))       
        DECLARE @idx INT, @result INT       
        DECLARE @slice VARCHAR(8000)       
        SET @String = LTRIM(RTRIM(@String))
        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 @result = (SELECT COUNT(Items) FROM @temptable)
    RETURN @result
END 

And  test follwing

declare @tmp table(Value VARCHAR(500))
 
insert into @tmp
select '123456 567890 123467'
union all
select '147852 258963'
union all
select '147852 258963 147852 258963'
union all
select '147852 258963 123456 567890 123467' 

SELECT dbo.[SplitAndCount](Value,' ') FROM @tmp