I need to find commonly used phrases in a text field in a SQL table (SQL 2008 R2)

I need to find commonly used phrases in a text field in a SQL table (SQL 2008 R2).  The text contain reports written by medical professionals. The output I would like is a listing of phrases in the text and how often they are used. For example, I would want the output to look something like this:
# of Occurrences  Phrase
120 - Continue with plan of care
119 - The patient complained of pain located
100 - Restrict lifting to a maximum of
..
...

I can run loops to find all of the length of phrases and dump phrases into a table and then query for a count of each one, but I know that will take a lot of time. Is there any way to do this more efficiently and faster?
LVL 1
loneieagle2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Did you try? A pass-through should be quite fast:

    Select Count(*) From TheTable Where TheField Like '%Continue with plan of care%'

/gustav
0
loneieagle2Author Commented:
Gustav,

I guess my question wasn't clear. I don't know what the phrases are yet. So I have to analyze the text and determine what the phrases are and how often they are used. My idea was to find individual words (by parsing based on the spaces between words) and storing those in a table. Then go through and find two word phrases and drop them in the table, etc until I have the longest phrases. Then I can run the query you suggest. So since the average text field contains about 200 words and there are about a million records in the table, I would be making 200 million passes thru whatever method I use to extract the phrases.

Bob
0
Gustav BrockCIOCommented:
I see. I don't think there is a smart way doing this other than the method you describe.

If it is a one-time operation, you could write some loops and let them run overnight.

/gustav
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
I would agree with Gustav, if you knew what phrases were in use, you could save those phrases to a table, and then do a query against those specific phrases, but without first knowing what phrases, you would have to parse each record into 2, 3, 4, 5, .... word phrases, and save those to a table.  

This would be a very time consuming task involving loops through all of the records, and then loops for the length of the phrase (# of words), and the start point within each record, which would overlap.  A single record of only 10 words would have 45 possible "phrases" of 2-10 words in length.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
@loneieagle2

Oops, I forgot to refresh the thread and didn't see your first response to Gustav before posting.
0
loneieagle2Author Commented:
Thanks, I was hoping for something better, but it looks like what I call the "Sledge Hammer" method is going to be the best.

Dale,

I can use what you told me to find out pretty close to how many phrases I will be creating to see if it is even feasible.

Bob
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.