Search code examples
javaandroidsqliteandroid-sqlite

run query on sqlite using a Set string

I am new with sqlite and database . I have an Set input. when I Log it , It looks like this :

D/tag input: [item1,item2,item3,item4]

now I want to make a query whit these items on my database . But I get nothing from it . here is my query :

 cursor = db.rawQuery("SELECT * from menu_items where menu_item_name=?", new String[]{input})

I don't know where I am doing wrong . Can someone help me on this case ? Thanks

Solution

  • Since input is a string that contains a comma separated list of strings, you can't use the operators = or IN.

    What you need is the operator LIKE.

    If in the value of input there are also the square brackets:

    cursor = db.rawQuery(
    "SELECT * FROM menu_items WHERE ',' || REPLACE(REPLACE(?, '[', ''), ']', '') || ',' LIKE '%,' || menu_item_name || ',%'", 
    new String[]{input}
);

    If not then the code can be simplified:

    cursor = db.rawQuery(
    "SELECT * FROM menu_items WHERE ',' || ? || ',' LIKE '%,' || menu_item_name || ',%'", 
    new String[]{input}
);

    If there is a space after each comma:

    cursor = db.rawQuery(
    "SELECT * FROM menu_items WHERE ', ' || ? || ', ' LIKE '%, ' || menu_item_name || ', %'", 
    new String[]{input}
);