How to use sql IN clause with sql LIKE

Question

I have spring boot application , in that in one of DAO class , we have one method where we are retrieving some data , for that we are using below query , here am using namedParameterJdbcTemplate

select student_id from student_records where create_date >= timestamp '"+appProps.getFromDate()+"' and  create_date <= timestamp '"+appProps.getToDate()+"' and student_id in (:studentIdList)

the complete method is like below

public List<String> readMatchingStudentIds_ALL(List<String> inputStudentIdsList){
            List<String> macthingStudentIdsList = new ArrayList<>();
            try{
                Map<String, List<String>> namedParameters = Collections.singletonMap("studentIdsList", inputStudentIdsList);
                String query = " select student_id from student_records where create_date >= timestamp '"+appProps.getFromDate()+"' and  create_date <= timestamp '"+appProps.getToDate()+"' and student_id in (:studentIdList)";

                macthingStudentIdsList =  namedParameterJdbcTemplate.queryForList(query, namedParameters, String.class);
            }catch(Exception e)
            {
                throw new RuntimeException(e.getMessage(),e);
            }
            return macthingStudentIdsList;
     }

everything working fine , but now problems coming , for example the incoming student is list contains "AFE1245" but in our db we have data like below "000AEF1245" , what i means 0's are prefixed , we do not know how many 0's are prefixed , for single query we can use LIKE operator like below

select student_id from student_records where student_id like '%input_student_id'

but my case is different as we need to use sql IN clause , is their any possibility we can use sql IN clause and LIKE both or is their any other way

`col1 IN(1,2,3 )` is just an abbreviated form of `( col1=1 or col1=2 or col1=3 )` and you **cannot** convert those equality tests to `LIKE` — Paul Maxwell, Nov 28 '18 at 06:11
@Used_By_Already Yes you can: `col like any(array[pat1, pat2, pat3])`. The real question is will the `:studentIdList` in `student_id like any(array[:studentIdList]))` be handled properly. — mu is too short, Nov 28 '18 at 08:12
`any(array` is not the same as `in ( ...)` the question asked for the latter. You have substituted that for a different feature. My comment above remains true, about the use of in() — Paul Maxwell, Nov 28 '18 at 10:33
@Used_By_Already You misunderstand. `col in (a, b, c)` is equivalent to `col = any(array[a, b, c])` (and PostgreSQL even converts `in` to `any(array[])`) so `col like any(array[pat1, pat2, pat3])` is the LIKE form of `in (list)`. — mu is too short, Nov 29 '18 at 01:42
@muistooshort [IN](https://www.postgresql.org/docs/current/functions-comparisons.html#FUNCTIONS-COMPARISONS-IN-SCALAR) <> [ANY](https://www.postgresql.org/docs/current/functions-comparisons.html#id-1.5.8.28.16) you have **substituted** the first for the second. Even the documentation on IN states *"This is a shorthand notation for expression = value1 OR expression = value2 OR ..."* try this: https://rextester.com/DUZZ23268 — Paul Maxwell, Nov 29 '18 at 02:20
@Used_By_Already But your own example shows that `in (...)` is equivalent to `= any(array[...])` (or since no pattern matching is involved, `like any(array[...])`). `c in ('a', 'b', 'c')`, `c = 'a' or c = 'b' or c = 'c'`, `c = any(array['a', 'b', 'c'])`, and even `c like any(array['a', 'b', 'c'])` are all equivalent. You can always substitute `= any(array[list])` for `in (list)` because they're two ways of writing the same thing, try this: https://rextester.com/JOTP30415 — mu is too short, Nov 29 '18 at 04:07
"*How to use sql IN clause with sql LIKE*" No amount of examples will assist. I was being "literal". The question asked for use of IN with LIKE. Substitution does not answer that very specific question. In fact by answering the question (as I did) it forces one to consider alternatives. May we end this now? — Paul Maxwell, Nov 29 '18 at 04:56

score 0 · Answer 1 · answered Nov 28 '18 at 06:04

0

You can do it by individual LIKEs with ORs:

SELECT student_id FROM student_records
WHERE student_id LIKE '%input_student_id'
OR  student_id LIKE 'input_student_id%'
OR  student_id LIKE '%input_student_id%';

Refer this

answered Nov 28 '18 at 06:04

Alien

15,141
6
37
57

How to use sql IN clause with sql LIKE

1 Answers1