How do I count the number of times a specific word appears in a text type field

Question

I've created a variable with the word that I'm searching for and used the replace command to replace any instances of this word with ''. However, it errors as the field where I'm applying the replace function is a text type field. How do I get around this.

DECLARE @one nvarchar(max) = 'PASSED'

SELECT
(LEN(itemnote.notes) - LEN(replace(itemnote.notes,@one ,'')))/LEN(@one ) AS passes
FROM itemnote.notes

Answer 1

Hi @Paula Houst
Are you trying to implement this statement in SSMS?
Since you added the SSRS tag, I can provide you with an expression in SSRS to count the number of occurrences of a specific word in a text type field.

=(LEN(Fields!QuickFox.Value) - LEN(REPLACE(Fields!QuickFox.Value, Parameters!CharToSearch.Value,"")))/Len(Parameters!CharToSearch.Value)  

As for your statement, I tested it in SSMS and it can work. I don't know what your specific problem is, maybe you can seek help under the T-sql tag: sql-server-transact-sql.html.
Best regards,
Aniya