Count number of occurrences of a phrase in ntext

Question

I am trying to create a function for counting the number of occurrences of a phrase in a ntext value.

This my function's code:

alter FUNCTION [dbo].[PhraseCounter] ( @pInput NTEXT, @pSearchPhrase NVARCHAR(100) )
RETURNS INT
BEGIN

DECLARE @vInputLength        INT
DECLARE @vIndex              INT
DECLARE @vCount              INT

SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = DataLength(@pInput)

WHILE @vIndex <= @vInputLength
BEGIN
    IF SUBSTRING(@pInput, @vIndex, 1) = @pSearchPhrase
        SET @vCount = @vCount + 1

    SET @vIndex = @vIndex + 1
END

RETURN @vCount

END

But it can't find anything in the input string.

Where is the problem in my function?

Answer 1

ALTER FUNCTION [dbo].[PhraseCounter] ( @pInput NTEXT, @pSearchPhrase NVARCHAR(100) )
RETURNS INT
BEGIN

DECLARE @vInputLength        INT
DECLARE @vIndex              INT
DECLARE @vCount              INT
DECLARE @vSerchLength        INT

SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = DataLength(@pInput)
SET @vSerchLength  = LEN(@pSearchPhrase)

WHILE @vIndex <= @vInputLength
BEGIN

    IF SUBSTRING(@pInput, @vIndex, @vSerchLength) = @pSearchPhrase
    BEGIN
        SET @vIndex = @vIndex + @vSerchLength
        SET @vCount = @vCount + 1
    END 
    ELSE 
    SET @vIndex = @vIndex + 1
END

RETURN @vCount

END
GO

If you do not need NTEXT, try:

DECLARE 
    @s1 NVARCHAR(MAX) = 'aaaabbbssss',
    @s2 NVARCHAR(MAX) = 'a'
SELECT (LEN(@s1) - LEN(REPLACE(@s1,@s2,'')))/LEN(@S2)