Case insesitive query with Springboot CRUD Repository

Values in Room_Entity table is hardcoded and Room_Status values are in uppercase . I want my search to be case insensitive. Here is my query. I am using upper but getting error

@Query(value = "SELECT TOP 1 r.ROOM_ID  FROM ROOM_ENTITY r WHERE r.ROOM_STATUS ='AVAILABLE'  AND r.ROOM_TYPE =:upper(roomType)", nativeQuery = true)
int findRoom(@Param("roomType") String roomType);

JavaScript
​x
 
@Query(value = "SELECT TOP 1 r.ROOM_ID  FROM ROOM_ENTITY r WHERE r.ROOM_STATUS ='AVAILABLE'  AND r.ROOM_TYPE =:upper(roomType)", nativeQuery = true)int findRoom(@Param("roomType") String roomType);​

Answer

Simply:

AND r.ROOM_TYPE = upper(:roomType)

JavaScript
 
AND r.ROOM_TYPE = upper(:roomType)​

But I’d consider using a case insensitive collation for the ROOM_TYPE column.

Advertisement

Answer