1

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?

1 Answer 1

2
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)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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