CHARINDEX keep returning 0

Question

does any one see why i am still getting 0 as a result of:

SELECT [KnowItAll].[dbo].[VSM_CanculateTermFrequency] (
    'hello hi hello by hello why'
    ,'hi')
GO

Where VSM_CanculateTermFrequency:

CREATE FUNCTION [dbo].[VSM_CanculateTermFrequency] 
(
    @i_Document NCHAR(4000),
    @i_Term NCHAR(30)
)
RETURNS SMALLINT
AS
BEGIN
    -- Declare the return variable here
    DECLARE @TermFrequency    SMALLINT
    DECLARE @pos              INT
    DECLARE @nextpos          INT

    SELECT @pos = 0, @nextpos = 1, @TermFrequency = 0

    WHILE @nextpos > 0
    BEGIN
        SELECT @nextpos = CHARINDEX(@i_Term,@i_Document)
        SELECT @TermFrequency = CASE
                                    WHEN @nextpos > 0 THEN @TermFrequency + 1
                                    ELSE @TermFrequency
                                END
        SELECT @pos = @nextpos
    END
    RETURN @TermFrequency
END

Answer 1

The problem is your use of nchar. In effect, you are not searching for 'hi', you are searching for

'hi                            '

You should change the datatype on your two parameters to nvarchar. By using nchar, the system pads the value with spaces so that it becomes 30 or 4000 characters.

Btw, another problem I see unrelated to CharIndex always returning zero issue (which is due to the padding), is that you are not telling CharIndex to search after the last found term. You should change your call to CharIndex to be:

CHARINDEX(@i_Term,@i_Document,@pos + 1)

(Btw, I see that Gabe found this first)