JPQL: cast Long to String to perform LIKE search

Question

I have the following JPQL query:

SELECT il
FROM InsiderList il
WHERE ( il.deleteFlag IS NULL OR il.deleteFlag = '0' )
  AND il.clientId = :clientId
  AND (    LOWER( il.name ) LIKE :searchTerm
        OR il.nbr LIKE :searchTerm
        OR LOWER( il.type ) LIKE :searchTerm
        OR LOWER( il.description ) LIKE :searchTerm )

The customer wants us to be able to search be the nbr field, which is a java.lang.Long.

Q:

How do you perform a LIKE search on a java.lang.Long using JPQL?

Answer 1

You can simply use CAST(num as string) or CONCAT(num,''). It worked for me

Answer 2

You can use the CAST in HQL:

SELECT il
FROM InsiderList il
WHERE ( il.deleteFlag IS NULL OR il.deleteFlag = '0' )
  AND il.clientId = :clientId
  AND (    LOWER( il.name ) LIKE :searchTerm
        OR CAST( il.nbr as string ) LIKE :searchTerm
        OR LOWER( il.type ) LIKE :searchTerm
        OR LOWER( il.description ) LIKE :searchTerm )

But you can have serious performance problems doing this, because the database will cannot use the nbr index (if nbr column is indexed).