Grouping results with similar content in SQL

I have a library of books in an SQL database. Some of these books contain phrases from the original books -- theses books are called compilations (compiled from the original source). When I do a "like" search in SQL, I am returned many duplicate results due to the compilations. I cannot get rid of, hide, or flag the compilations in the database because this may remove some valid source material that is being searched for.

How would I group these results so that I am returned unique phrases that contain the searched for text rather than the same phrases from multiple compilations? What is the best way to accomplish this? The "Group By" statement wont work, some similar records are slightly different due to an ending reference at the end.

Right now the data is in an SQLlite database I am assuming I can import into MSSQL or MySQL
DanNetwork EngineerAsked:
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.

Tomas Helgi JohannssonCommented:

You could use the MySQL built-in group_concat function to group together every result for each unique phrases which you can then process further.
Something like this (phsudo query)

select myuniquephrase, group_concat(sometextcolumn)
from table
group by myuniquephrase

Hope this helps.

    Tomas Helgi
group_concat exists in SQLite also, ref:

note however (regardless of dbms) filteing by like is relatively 'expensive' already and adding group_concat may slow the query(ies) further.

Using that function might satisfy the need, but when you say "I am returned many duplicate results due to the compilations" are you indicating many "similar results" or true duplication?

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
DanNetwork EngineerAuthor Commented:
Thanks guys for your help.
A pleasure, thanks for the question. Cheers, Paul
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 Development

From novice to tech pro — start learning today.