How to find the most common phrases in a varchar field

Question

I have a table which contains support tickets. Each ticket has a title and many of them have common phrases in them (XXX cannot connect to YYY, XXX requires a new YYY, XXX requires upgrading, etc. etc.)

I would like to do some analysis on the data and extract these common phrases along with a count of their usage.

Repeated character sequences would be acceptable, but ideally I would want to split the title into words and identify repeated sequences of words. Here is a simple example:

Title
------------------------------------------------
John cannot connect to SERVER01
The backup service cannot connect to the client
Mary cannot connect her laptop to the network

From this data I would like to be able to extract the following:

Phrase                                             Count
-------------------------------------------------- -------
cannot connect                                     3
cannot connect to                                  2

I have a plan to do this using a c# application and brute force, but can anyone suggest a more elegant solution?

Answer 1

So I ended up solving this with a C# application. I thought I would share my code in case anyone else needed something similar. It's using Entity Framework to do the data access.

        using (var ticketsContext = new CMS.Data.Tickets.TicketsContext("name=CMS"))
        {
            using (var analysisContext = new TicketTitleAnalysis.Analysis())
            {
                int lastTicketID = analysisContext.Phrases.Max(p => (int?)p.LastTicketID).GetValueOrDefault();

                var tickets = (from t in ticketsContext.Tickets where t.TicketID > lastTicketID orderby t.TicketID select new { t.TicketID, t.Title });

                foreach (var ticket in tickets)
                {
                    var titleWords = ticket.Title.ToLower().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries).Where(w => char.IsLetter(w[0])).ToArray();

                    for (int startingWord = 0; startingWord < titleWords.Length; startingWord++)
                    {
                        for (int numberOfWords = 1; numberOfWords <= titleWords.Length - startingWord; numberOfWords++)
                        {
                            var words = string.Join(" ", titleWords.Skip(startingWord).Take(numberOfWords));

                            if (words.Length > 5)
                            {
                                var phrase = analysisContext.Phrases.FirstOrDefault(p => p.Words == words);

                                if (phrase == null)
                                {
                                    phrase = new Phrase();
                                    phrase.Words = words;

                                    analysisContext.Phrases.Add(phrase);
                                }

                                phrase.LastTicketID = ticket.TicketID;
                                phrase.Count++;
                            }
                        }
                    }

                    analysisContext.SaveChanges();
                }
            }
        }