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
afactsNetwork EngineerAsked:
Who is Participating?
PortletPaulConnect With a Mentor Commented:
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?
Tomas Helgi JohannssonConnect With a Mentor Commented:

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

All Courses

From novice to tech pro — start learning today.