Sql-server – Function is counting -1 char ( wrong )

sql-server-2008-r2

I have this function ( It counts how many characters I have in a certain string, separated by ,:

ALTER FUNCTION [dbo].[fContaRespostas]     
(    
 @List VARCHAR(6000),@SplitOn VARCHAR(5),@Resposta VARCHAR(50)    
)      
returns integer  
AS
BEGIN   
Declare @Qtd int 
Set @Qtd = 0

Declare @Value varchar(255) 
Set @Value = ''

WHILE (Charindex(@SplitOn,@List)>0)    
 BEGIN      

    Set @Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))    

    IF ( @Value = @Resposta)
        begin 
            Set @Qtd = @Qtd + 1
        end
    SET @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))    

 END         

 return @Qtd
END

This is the test. It needs to display 8, But it shows 7.

select dbo.fContaRespostas('5,5,5,5,55,5,5,5,5',',','5') as Qtd

If i use , in the end, it counts as 8 5.

select dbo.fContaRespostas('5,5,5,5,55,5,5,5,5,',',','5') as Qtd

What's wrong in the code? I really can't find it.

Best Answer

The problem with your code is probably that the logic looks for the @SplitOn character to know where the end of the SUBSTRING should be. The fix would be to simply add the @SplitOn character to the end of @List at the start of the function.

HOWEVER, I think you are going about the operation in the wrong way. First, splitting using a WHILE loop is horribly inefficient, especially since you have to recalculate @List in each iteration of the loop.

You would be far better off using an efficient string splitter and then doing a COUNT(*) combined with a WHERE val = '5'.

For example:

SELECT COUNT(*)
FROM   SQL#.String_Split4k(N'5,5,5,5,55,5,5,5,5', N',', 1) split
WHERE  split.SplitVal = N'5';
-- 8

Please note that the example uses a SQLCLR function from the SQL# library (which I am the author of, but String_Split4k is in the Free version) to do the string splitting.

Related Question