1

I am using Spring-Boot 1.5.10 with JPA connected to SQL Server 2016. I do have a custom query which is executing a LIKE but when the fields is NULL no records are returned:

@Query("SELECT s FROM Speaker s where " +
        "(" +
        "lower(s.lastName) like lower(concat('%', ?1,'%')) " +
        "OR lower(s.firstName) like lower(concat('%', ?1,'%')) " +
        "OR lower(s.biography) like lower(concat('%', ?1,'%'))" +
        ") " +
        "and lower(s.language) like lower(concat('%', ?2,'%')) " +
        "and lower(s.contactType) like lower(concat('%', ?3,'%')) " +
        "and s.fee <= ?4")
Page<Speaker> findByContains(String criteria, String language, String type, int fee, Pageable pageable);

The Query is connected to a REST controller where 1 parameter is passed called criteria, the problem is that you might call the REST controller without passing any criteria at all. In that scenario the query doesn't return records where one of the search criteria has NULL in the Database.

Raffaeu
  • 6,694
  • 13
  • 68
  • 110

1 Answers1

3

You could use COALESCE to transform null into the emtpy string:

lower(COALESCE(s.firstName,''))

etc.

Turo
  • 4,724
  • 2
  • 14
  • 27
  • There you go, that's exactly what I was looking for. The problem is on SQL Server and not on the JPA query – Raffaeu Jan 03 '19 at 12:20
  • JPA does not convert into NULL in case of ORACLE. So this will not work if you are using Oracle as DB. – Kiran Joshi Oct 14 '22 at 06:49
  • @KiranJoshi That's one thing I find very annoying from Oracle, luckily the question is tagged with sql-server :-) – Turo Oct 14 '22 at 19:04