CHARINDEX keep returning 0

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

---

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)

---

Once you change your datatype to nvarchar, you will discover that you have an infinite loop because it always starts searching from the beginning of the document. Your line should read:

SELECT @nextpos = CHARINDEX(@i_Term,@i_Document, @pos + 1)