0
I have an appointment SELECT NOME FROM PESSOA WHERE CODIGO = 1
She returns to me ANA ANA JOAO ANA ALBERTO ANA
There is a way to count the events of ana?
Using only the sql query?
1 answer
3
There is a way to count the events of ana (...) using only the sql query?
my select returns only a row 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 column NAME in tokens, using type function split string, and then count the number of occurrences of the token.
-- 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;
In code #2 the function was used split string Splitstrings_moden, available in the article 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 type function.
Code #2 can be easily changed to allow counting 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;
To deepen the subject, I suggest reading the article "Separating multi-valued text content (split string)”.
Browser other questions tagged sql sql-server query
You are not signed in. Login or sign up in order to post.
select Count(*) from person Where code = 1 and name = 'ANA'
– Motta
@Jhonatanjorgedelima: What does the CODIGO column contain? Is it the primary key of the PESSOA table? // The query you posted as an example returned a single line with the names or several lines, each with a name? // What is the COLLATE of the NAME column?
– José Diz
@Jhonatan, does the NAME column contain only a single word? Could you explain the context better? The description seems very vague to me.
– José Diz
Friends this is just an example, the above query literally behind that written by complete in the field "ana ana ana ana Alberto ana" ,- returns a single line with this information, really is in a single field, is not returned several lines
– Jhonatan Jorge de Lima