Count number of occurrences of a phrase in ntext
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 &lt;= @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)