Search for Emoji with JPQL

Hibernate ORM
1

We often use JPQL statements in conjunction with Hibernate for searching in our application.
While searching for an EMojis in a text column of a MSSQL database we encountered the the problem that the search returned too many results.
SELECT e.description FROM xyz e WHERE e.description like '%the emoji%'

So we did it directly on the DB with the SQL statement
SELECT description FROM xyz WHERE description like N'%the emoji%' COLLATE Latin1_General_BIN2
that worked fine. But if we try to append the COLLATE to the JPQL statement we get the following error

Root cause:
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: COLLATE near line 13, column 58

Is their an (upcoming) solution for search for an emoji with JPQL, Hibernate and MSSQL ?

3

Hibernate 6.0 will have support for the collate clause. In 5.x you could create a custom SQLFunction that renders the collate clause to an expression and use it like this COLLATE('%the emoji%', 'Latin1_general_BIN2')

4

Thanks, for the information. We actual uses Hibernate 5.4, so we have to upgrade.