MaxKroy
asked on
SQL Query to find keywords and count
Looking for a good sql query that will pull out keywords from a paragraph and count how many times they occur.
For example:
KeywordTable:
Word
the
and
a
like
Paragraph:
Conscientiousness is one of the five traits of the Five Factor Model of personality, and is an aspect of what has traditionally been referred to as having character. Conscientious individuals are generally hard working and reliable. When taken to an extreme, they may also be "workaholics", perfectionists, and compulsive in their behavior. People who score low on conscientiousness tend to be more laid back, less goal-oriented, and less driven by success; they also are more likely to engage in antisocial and criminal behavior.
Outcome:
the, 5
and, 3
etc.
SELECT Word, COUNT(Word) AS count FROM KeywordTable WHERE....(any of the words in the keyword table are in the paragraph and their count)
Thanks in advance!!!
D-
For example:
KeywordTable:
Word
the
and
a
like
Paragraph:
Conscientiousness is one of the five traits of the Five Factor Model of personality, and is an aspect of what has traditionally been referred to as having character. Conscientious individuals are generally hard working and reliable. When taken to an extreme, they may also be "workaholics", perfectionists, and compulsive in their behavior. People who score low on conscientiousness tend to be more laid back, less goal-oriented, and less driven by success; they also are more likely to engage in antisocial and criminal behavior.
Outcome:
the, 5
and, 3
etc.
SELECT Word, COUNT(Word) AS count FROM KeywordTable WHERE....(any of the words in the keyword table are in the paragraph and their count)
Thanks in advance!!!
D-
Hi,
Generally SQL is the wrong tool for this. You can use a hammer to pound in screws, and a screwdriver to pound nails, but neither is very successful at that.
In what form would the paragraph be? Individual words in a table, or as a one value in a varchar( max ) column or variable?
Maybe a CLR function using the .net libraries for string manipulation would be better here.
HTH
David
Generally SQL is the wrong tool for this. You can use a hammer to pound in screws, and a screwdriver to pound nails, but neither is very successful at that.
In what form would the paragraph be? Individual words in a table, or as a one value in a varchar( max ) column or variable?
Maybe a CLR function using the .net libraries for string manipulation would be better here.
HTH
David
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DECLARE @Text AS VarChar(MAX)
SET @Text = 'This is a sentence'
PRINT 'Number of words: ' + CAST(LEN(@Text) - LEN(REPLACE(@Text, ' ', '')) +1 AS VarChar(3))