Word count for all words displayed in a SQL Server 2008 column

Question

I have a table called 'ticket_diary_comment'with a column with a name 'comment_text'. This column is filled with text data. I would like to get the frequency of all the words found throughout the column. Example:

Comment_Text
I am a good guy
I am a bad guy
I am not a guy

What I want:

Word    Frequency
I       3
good    1
bad     1
not     1
guy     3

Please note that I also deleted the stop words on the output. I know that calculating the frequency of a particular word is not difficult, but I'm looking for something that takes into account all the words appearing in the column that remove the stop words.

I would appreciate any help on this issue. I would also like to mention that I have to apply this query to a large dataset (about 1 TB), so performance is a problem.

Answer 1

, , . - :

SELECT item, count(1)
FROM ticket_diary_comment 
    CROSS APPLY dbo.fn_SplitString(comment_text, ' ')
GROUP BY item

fn_SplitString:

CREATE FUNCTION [dbo].[fn_SplitString]   
(   
    @String VARCHAR(8000),   
    @Delimiter VARCHAR(255)   
)   
RETURNS   
@Results TABLE   
(   
    ID INT IDENTITY(1, 1),   
    Item VARCHAR(8000)   
)   
AS   
BEGIN   
INSERT INTO @Results (Item)   
SELECT SUBSTRING(@String+@Delimiter, num,   
    CHARINDEX(@Delimiter, @String+@Delimiter, num) - num)   
FROM Numbers   
WHERE num <= LEN(REPLACE(@String,' ','|'))   
AND SUBSTRING(@Delimiter + @String,   
            num,   
            LEN(REPLACE(@delimiter,' ','|'))) = @Delimiter   
ORDER BY num RETURN   
END   

numbers, CREATE TABLE Numbers(Num int) 1 10000 ( /). ). , / , .