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  
Declare @Qtd int 
Set @Qtd = 0

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

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

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

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


 return @Qtd

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.


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:

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.