SQL query to bring all results regardless of punctuation with JSF

Question

So I have a database with articles in them and the user should be able to search for a keyword they input and the search should find any articles with that word in it.

So for example if someone were to search for the word Alzheimer's I would want it to return articles with the word spell in any way regardless of the apostrophe so;

Alzheimer's
Alzheimers

results should all be returned. At the minute it is search for the exact way the word is spell and wont bring results back if it has punctuation.

So what I have at the minute for the query is:

 private static final String QUERY_FIND_BY_SEARCH_TEXT = "SELECT o FROM EmailArticle o where UPPER(o.headline) LIKE :headline OR UPPER(o.implication) LIKE :implication OR UPPER(o.summary) LIKE :summary";

And the user's input is called 'searchText' which comes from the input box.

 public static List<EmailArticle> findAllEmailArticlesByHeadlineOrSummaryOrImplication(String searchText) {
    Query query = entityManager().createQuery(QUERY_FIND_BY_SEARCH_TEXT, EmailArticle.class);

      String searchTextUpperCase = "%" + searchText.toUpperCase() + "%";
    query.setParameter("headline", searchTextUpperCase);
    query.setParameter("implication", searchTextUpperCase);
    query.setParameter("summary", searchTextUpperCase);
    List<EmailArticle> emailArticles = query.getResultList();
    return emailArticles;
}

So I would like to bring back all results for alzheimer's regardless of weather their is an apostrophe or not. I think I have given enough information but if you need more just say. Not really sure where to go with it or how to do it, is it possible to just replace/remove all punctuation or just apostrophes from a user search?

Answer 1

In my point of view, you should change your query, you should add alter your table and add a FULLTEXT index to your columns (headline, implication, summary).

You should also use MATCH-AGAINST rather than using LIKE query and most important, read about SOUNDEX() syntax, very beautiful syntax.

All I can give you is a native query example:

SELECT o.* FROM email_article o WHERE MATCH(o.headline, o.implication, o.summary) AGAINST('your-text') OR SOUNDEX(o.headline) LIKE SOUNDEX('your-text') OR SOUNDEX(o.implication) LIKE SOUNDEX('your-text') OR SOUNDEX(o.summary) LIKE SOUNDEX('your-text') ;

Though it won't give you results like Google search but it works to some extent. Let me know what you think.