How to order by number of keywords found in sql/namedQuery

Question

I created a query with a like and a or

select * from xxx where title like ('%bon%') or title like ('%test%')

I receive a result with : bonjour je test bonjour test bonjour test

And I would like ordered by number of word in title : bonjour je test test bonjour bonjour test

I use Grails with namedQueries.

Is it possible to do that ?

Thanks

Answer 1

You can use this little trick to count the number of space characters and sort by that.

select * from xxx where title like ('%bon%') or title like ('%test%')
order by (LEN(title) - LEN(REPLACE(title, ' ', '')))