SQL Server - calculate number of word occurrences in a column, per record

Question

My goal is to create a select to return the total occurrences of a word, a column, per record

My table is modeled as follows: id / filename / qntd_pg / content

ex: I want to search how many times the word 'Rio' appears in the column [content], for each bank record.

The code below gives no error, but returns the amount as Null. It’s the first time I’m using the if structure in Sql Server.

Follows the Code:

declare @palavra varchar(30),
        @cont int,
        @result int
select
    [DCM_conteudo]  
FROM [dcm_digital].[dbo].[conteudo_dcm] 
where upper([DCM_conteudo]) like '%Rio%' and [DCM_id] = 1
    if  @palavra = 'Rio' 
    begin
        select @result = count(@cont);
    end;    
    select @result;

---

Updating...

Solution in the post: SQL Server - Return only the searched word

Answer 1

Renata, I made some adjustments to use locally. You will need to change the table used, but this is simple. You can use it as follows:

DECLARE @conteudo_dcm TABLE (DCM_id INT IDENTITY(1,1), DCM_conteudo VARCHAR(100))

INSERT INTO @conteudo_dcm VALUES ('Rio e a RIO vai se repetir então teremos 2 :D')
INSERT INTO @conteudo_dcm VALUES ('um Rio apenas... então teremos 1 :D')
INSERT INTO @conteudo_dcm VALUES ('tres pra este caso. Rio, Rio, Rio XD.. então 3.. blz?')

declare @palavra varchar(30) = 'Rio',
        @cont int,
        @result int,
        @palavraLike varchar(30) = '%Rio%'
SELECT
    [DCM_conteudo],
    (LEN([DCM_conteudo]) - LEN(REPLACE([DCM_conteudo], @palavra, ''))) /
            LEN(@palavra)
FROM @conteudo_dcm
where upper([DCM_conteudo]) like @palavraLike-- and [DCM_id] = 1

In this case I am counting as I locate the word in the string for each line.

Answer 2

Try if you only want to count the amount of records that have the word 'Rio' ever, that is, when there are 2 times in my Row will count only one:

declare @palavra varchar(30),
        @result int

select
    @result = Count([DCM_conteudo]) -- Set a variavel Result com a quantidade de registros retornados
    FROM [dcm_digital].[dbo].[conteudo_dcm] 
    where upper([DCM_conteudo]) like '%'+@palavra+'%' -- se necessario and [DCM_id] = 1

select @result; -- Apresenta o resultado