The description of a person can be up to 500 characters.
I then run a query like "from Person p where p.description like '%chocolate%'".
Say i have 4 people in the database whose descriptions are as follows:
Person 1 description - I really love chocolate.
Person 2 description - chocolate is my favorite thing, especially dark chocolate.
Person 3 description - I like long walks.
Person 4 description - chocolate, chocolate, i absolutelly love chocolate
This query will then return people 1, 2 and 4 but not 3 as 3's description does not contain the word chocolate.
So what my question is, is that i want to create a counter for each row showing how many times chocolate occured in that persons description. I then also want to order by this counter. For instance the counters for the 4 people would be the number of times chocolate appeared in their decsription.
Person 1 - 1 Person 2 - 2 Person 3 - 0 Person 4 - 3
Does anyone know how to do this and how especially to do it with criteria and HQL. If someone knows where an example or documentation, or what the technical term for this dynamic counting please could you let me know. Is this even possible using a relational database.