i need to combine like and in query ..for e.g some thing like select * from table where first_name in ('%abc%','%def%')
select * from table where lower(first_name) like any(array['%neh%','%bip%']);
this is not working in jpa
Asked
Active
Viewed 31 times
0
-
1Would `first_name like X or first_name like Y` be ok or do you need to append an arbitrary number of possibilities? – Federico klez Culloca Jan 17 '23 at 09:09
-
2It seems that SQL itself doesn't support this, see https://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql for hints on how to work around it. – Thomas Kläger Jan 17 '23 at 09:16
-
first_name like X or first_name like Y ,.......can be arbitary number of possibilities – Bipul Thapa Jan 17 '23 at 09:23
-
*can be arbitary number of possibilities* Then you probably need to build your SQL string and your parameter list dynamically. – Ole V.V. Jan 17 '23 at 09:40
-
it wont take ['%neh%','%bip%'] as array if this is build dynamically – Bipul Thapa Jan 17 '23 at 10:27
-
1lower(u.first_name) like any(array[string_to_array(?2,',')])...dynamically building parameter list worked – Bipul Thapa Jan 18 '23 at 09:45