search using regex in JPA

Question

I am trying to do a simple search function by multiple words, like this in SQL query

SELECT * FROM faqs WHERE title REGEXP 'préférée|Changer|endommagé' or question REGEXP 'préférée|Changer|endommagé'

but when I tried to implement this in @Query in my JpaRepository like this

@Query(value = "SELECT f FROM Faq f WHERE f.title REGEXP :term" )
Page<Faq> searchByRegExp(@Param("term") String term,Pageable pageable);

But it seems that REGEXP isnt supported because of this error:

<expression>, <operator>, GROUP, HAVING or ORDER expected, got 'REGEXP'

Note: I tried adding nativeQuery = true - same issue:

@Query(value = "SELECT * FROM faqs WHERE title REGEXP :term ", nativeQuery = true)
    Page<Faq> searchByRegExp(@Param("term") String term,Pageable pageable);

Answer 1

Unfortunately jpql does not support regular expressions. You would have to use like and write all the possibilities. But in your case you can simply use in:

@Query(value = "SELECT f FROM Faq f WHERE f.title in (:terms)" )
Page<Faq> searchByRegExp(@Param("terms") List<String> terms, Pageable pageable);

And as the first parameter pass the possibilities: List.of("préférée", "Changer", "endommagé"). For more complex regular expressions this list would grow considerably, but in this case only 3 values are possible.

The other way is using nativeQuery and sql:

@Query(value = "SELECT f.* FROM faqs f WHERE (f.title REGEXP :term)", nativeQuery = true)
Page<Faq> searchByRegExp(@Param("term") String term,Pageable pageable);