word occurrence in sql query

  

Is there a way to count the occurrences of the ana (...) using only the sql query?
my select returns only one line and a column and in this field comes this string literally as content "ANA ANA JOAO ANA ALBERTO ANA"

Jhonatan, considering the additional explanations, it seems to me that the solution is to break the contents of the NAME column into tokens , using function of type split string , and then count the number of token occurrences.

-- código #2 v3
with unpPESSOA as (
SELECT S.Item as Token
  from PESSOA as P
       cross apply dbo.SplitStrings_Moden (replace(P.NOME, ' ', '\'), '\') as S
  where P.CODIGO = 1
)
SELECT Token, count(*) as Qtd
  from unpPESSOA
  where Token = 'ANA' 
  group by Token;  

Incode#2,theSplitStrings_Modensplitstringfunctionwasavailableinarticle Split strings the right way - or the next best way .

If there is more than one space between tokens , it is necessary to transform them into a single space, using Alltrim function.

Code # 2 can be easily changed to allow counting of the number of occurrences of each token in each row of the table:

-- código #3
with unpPESSOA as (
SELECT CODIGO, S.Item as Token
  from PESSOA as P
       cross apply dbo.SplitStrings_Moden (replace(P.NOME, ' ', '\'), '\') as S
)
SELECT CODIGO, Token, count(*) as Qtd
  from unpPESSOA
  group by CODIGO, Token;  

Select count(*) from pessoa where nome like 'ana' 

Remembering that if you need to use wildcard % and upper / lower case