If you will use it in MySQL directly, the syntax is like this:
select * from person where last_name sounds like 'Doe' and first_name sounds like 'John';However, we can't do this in hibernate query language:
from Person where p.lastName sounds like 'Doe' and p.firstName sounds like 'John';HQL does not understand sounds and like together.
A workaround is to write a stored function. Create sp.sql with contents:
DELIMITER $$ DROP FUNCTION IF EXISTS fuzzy_match $$ CREATE FUNCTION fuzzy_match (s1 VARCHAR(255), s2 VARCHAR(255)) RETURNS BOOL DETERMINISTIC BEGIN RETURN s1 sounds like s2; END $$ DELIMITER ;
Save to your database instance:
$ mysql -u root -ppassword myschema < sp.sql
And now you can revise your HQL in Grails or Hibernate project:
def lastName = 'Doe' def firstName = 'John' def list = Person.executeQuery("from Person p where fuzzy_match(p.lastName, :lastName) > 0 and fuzzy_match(p.firstName, :firstName) > 0 ", [lastName:lastName, firstName:firstName])Simple and fast!