HQL Querying, finding by string idea

Two questions.

1. Is there a way for us to query by substring/contains method or like a search style query or even regular expression matching? Without making it case sensitive?!!! Example.

Query for: Good Food:

Table contains

1. I like eating Good Foo开发者_Go百科d.
2. Blah blah blah.
3. Good Food is great.
4. I like mcdonalds.
5. Food is Good.

The result of results I would recieve is 1,3 the rest is pretty much useless.

Second Question.

I have a table with

Primary Key      EventId          Message
1                  14             Good Food now
2                  14             Good Food later
3                  14             Good food today
4                  17             scrabble
5                  19             babble
6                  20             squable

I pretty much want the event id that is associated when a message contains good food. So Rather than retrieving eventId 3 times. I just want eventid 1 time with the number 14. is that possible too?

So in short.

When querying for good food inside a message, can i just get event Id that returns 14 rather than 3 rows of 14?

To remove the case sensitivity, you can use ILIKE in some rdbms but as far as i know not in oracle. You can use upper/lower to work around that.

You can use 'distinct' on EventId to solve the last part of your task but be aware that distinct may become expensive. This finding might even lead to rethinking your data model.