Mysql query with In clause regex

Question

I have a SQL query like this:

"select f.filterid as filtename, f.id as filtertext " +
    "from filter f " + 
    "where group_Id = '" + id +"' " + 
    "OR groupIds like '%." + id + ".%' ";

And I want to pass a list of ids to this query to make performance better. I don't know whether REGEX works with in an IN clause. And I tried the below one which is not working and not sure what to use in case of REGEX.

"select f.filterid as filtename, f.id as filtertext from filter f " + 
    "where group_Id in ("+StringUtils.join(ids, "','")+")" + 
    "OR groupIds in ("+StringUtils.join(ids, "','")+")"";

Thanks.

group_Id is a numeric datatype of varchar data type? – mhasan Nov 08 '16 at 09:00 — mhasan, Nov 08 '16 at 09:00
group_Id is int and groupIds is varchar – rakeeee Nov 08 '16 at 09:02 — rakeeee, Nov 08 '16 at 09:02

mhasan · Answer 1 · 2016-11-08T09:17:23.053

1

I would recommend to use the Query#setParameter to achieve this, if you are using JPA you can easily supply your ids list in the setParameter.

But for your current resolution you may try the below changes.

Not sure if your group_Id column expects integer or string datatype, well I will propose changes for either of the cases.

If it expects String - You are missing the starting " ' " change your code as below

If it expects integer type - You should not wrap your comma separator with " ' ", remove them as below

"select f.filterid as filtename, f.id as filtertext from filter f " + "where group_Id in ("+StringUtils.join(ids, ",")+")" + "OR groupIds in ("+"'"+StringUtils.join(ids, "','")+"'"+")";

Trying running this query and see if you get the desired resultset

edited Nov 08 '16 at 09:17

answered Nov 08 '16 at 09:10

mhasan

3,703
1
18
37

I need to pass list of regex string for groupIds which i am not able to do it in plain sql select query. – rakeeee Nov 08 '16 at 09:36
How your regex string looks like? – mhasan Nov 08 '16 at 09:41
It looks like 1.12345.2 and i am only interested in like doing with list of %.12345.% values. See this post here. http://stackoverflow.com/questions/9540087/can-the-in-operator-use-like-wildcards-in-oracle – rakeeee Nov 08 '16 at 09:46
You cannot use LIKE with IN clause in MySQL DB, option for you would be to repeat the LIKE clause for the number of elements in your groupIds list – mhasan Nov 08 '16 at 09:51
if i have a list of one million then the query looks awful. – rakeeee Nov 08 '16 at 09:53
You cannot pass more 1000 params in your IN clause, that's a restriction from DB, in such case you better of dividing your list into chunks of 500 or 1000 records list and then iteratively call your select on it – mhasan Nov 08 '16 at 09:55

goodyang · Answer 2 · 2016-11-08T09:38:38.803

Perhaps the problem lies in the use of method StringUtils.join. you can edit your sql like the following code.

 select f.filterid as filtename, f.id as filtertext from filter f where group_Id in ('groupA_id', 'groupB_id', 'groupC_id')

if your ids is {"groupA_id", "groupB_id", "groupC_id"}, then

"select f.filterid as filtename, f.id as filtertext from filter f where group_Id in (" + "'" + StringUtils.join(ids, "','") + "'" +")"

Ankit Deshpande · Answer 3 · 2016-11-08T09:50:18.697

0

Try Something like this:

Query query = session.Query("select f.filterid as filtename, f.id as filtertext from filter f where group_Id in :list");
query.SetParameterList(":list", ListOfIds);

edited Nov 08 '16 at 09:50

answered Nov 08 '16 at 09:44

Ankit Deshpande

3,476
1
29
42

Mysql query with In clause regex

3 Answers3