Android SQLiteDatabase query with LIKE

Question

I have 3 names, Allakhazam, Beatbox and Cunning in my NAMES Table.

public Cursor fetchNamesByConstraint(String filter) {

    mDb.query(true, DATABASE_NAMES_TABLE, new String[] { KEY_ROWID,
            KEY_NAME }, KEY_NAME + " LIKE ?",
            new String[] { filter }, null, null, null,
            null);

    return mCursor;
}

I call the function with "A" as the filter, but my cursor is returning a 0 count when it should at least return me a 1. Anyone can see what's wrong with the code?

jeet · Accepted Answer · 2012-01-31T08:54:16.070

106

this statement will return all the records whose keyname equals string specified by string, if you use wild card, then you can get desired results. Like:

mDb.query(true, DATABASE_NAMES_TABLE, new String[] { KEY_ROWID,
            KEY_NAME }, KEY_NAME + " LIKE ?",
            new String[] { filter+"%" }, null, null, null,
            null);

Will Lists all the records starting with word in filter.

mDb.query(true, DATABASE_NAMES_TABLE, new String[] { KEY_ROWID,
            KEY_NAME }, KEY_NAME + " LIKE ?",
            new String[] {"%"+ filter+ "%" }, null, null, null,
            null);

Will Lists all the records containing word in filter.

edited Jan 31 '12 at 08:54

answered Jan 31 '12 at 08:44

jeet

29,001
6
52
53

1

Thanks, this did it for me, I was looking at the wrong place. – Maurice Jan 31 '12 at 09:04
Why do we need to add % symbol to the filter in the above code? @jeet – srujan maddula Mar 04 '15 at 05:37
Because % is wildcard. The * is used for other elements in queries. – Tatarize Aug 08 '16 at 08:26

score 6 · Answer 2 · edited Mar 01 '16 at 11:49

    public java.util.Vector<Products> getsearch(String subcategory,String searchby)
    {
        SQLiteDatabase db=this.getReadableDatabase();

        Cursor cursor = db.query(
            TABLE_PRODUCTS, 
            new String[] { SUBCATEGORY, MAIN_CATEGORY, PRODUCT_ID, PRODUCT_NAME, BRAND, PACKAGE_SIZE, PRICE }, 
            SUBCATEGORY + " LIKE '%" + subcategory + "%'",
            null, null, null, null, null);
    }

score 4 · Answer 3 · answered Mar 23 '14 at 05:08

4

Can you try this.....code.......

public static final String KEY_ROWID="row";
public static final String KEY_NAME="name";

public Cursor fetchNamesByConstraint(String filter) {

  Cursor cursor=mDb.query(true, DATABASE_NAMES_TABLE, null,"row LIKE '%"+filter+"%' or name LIKE '%"+filter+"%'",null, null, null, null);

  }

answered Mar 23 '14 at 05:08

Mahmudul Haque Khan

426
4
19

4

Without parameter binding code become unsafe with SQL injection vulnerability – Alfishe Dec 29 '14 at 02:57
sorry i can not understand. – Mahmudul Haque Khan Dec 31 '14 at 07:48
3

I'll just pass filter = "criteria%'; DROP DATABASE .....; SELECT * FROM table WHERE row LIKE '%criteria" – Alfishe Dec 31 '14 at 23:36

Masidul Hasan · Answer 4 · 2020-11-23T15:41:15.707

2

You can use Raw Queries.

public Cursor fetchNamesByConstraint(String filter) {
    String query = "SELECT * FROM " + TABLE_NAME + " WHERE " +
                    COLUMN_NAME + " LIKE '%" + filter + "%'" ;
    SQLiteDatabase db = this.getReadableDatabase();
    cursor = db.rawQuery(query, null);
    return mCursor;
}

edited Nov 23 '20 at 15:41

answered Nov 23 '20 at 15:18

Masidul Hasan

41
3

score 1 · Answer 5 · answered Jan 31 '12 at 08:45

1

You should give the filter with wildcards ;)

"A%"

answered Jan 31 '12 at 08:45

jaanus

99
1
1
6

android.database.sqlite.SQLiteException: near "%": syntax error: , while compiling: SELECT DISTINCT _id, nid, name FROM names WHERE name LIKE %?% Getting this error when I add " LIKE ?%" – Maurice Jan 31 '12 at 08:49
you need to wrap your strings with single quotes – rsaxvc Apr 10 '13 at 15:44

score 0 · Answer 6 · answered Dec 17 '18 at 11:21

The rest is up to our desing, cursor is commanly same

public ArrayList<AboneDataList> getAllPasssiveAboneByDate(String mDate) {
    ArrayList<AboneDataList> foodList = new ArrayList<>();
    AboneDataList food;
    SQLiteDatabase database = dbHelper.getReadableDatabase();
    final String kolonlar[] = {DBHelper.COLUMN_A_ID,
            DBHelper.COLUMN_A_ABONE_NO,
            DBHelper.COLUMN_A_NAME,
            DBHelper.COLUMN_A_IS_STUFF,
            DBHelper.COLUMN_A_COUNTRY,
            DBHelper.COLUMN_A_CITY,
            DBHelper.COLUMN_A_TOWN,
            DBHelper.COLUMN_A_ADDRESS,
            DBHelper.COLUMN_A_PHONE,
            DBHelper.COLUMN_A_MOBILE,
            DBHelper.COLUMN_A_DATE_START,
            DBHelper.COLUMN_A_DATE_END,
            DBHelper.COLUMN_A_COUNT,
            DBHelper.COLUMN_A_IS_ACTIVE,
            DBHelper.COLUMN_A_CARGO,
            DBHelper.COLUMN_A_YURT_ID,
            DBHelper.COLUMN_A_JOURNAL,
            DBHelper.COLUMN_A_MAIL,
            DBHelper.COLUMN_A_BUSINESS,
            DBHelper.COLUMN_A_BIRTH,
            DBHelper.COLUMN_A_GENDER,
            DBHelper.COLUMN_A_ABONE_TYPE,
            DBHelper.COLUMN_A_MEDENI_TYPE};

    //String whereClause = DBHelper.COLUMN_A_JOURNAL + " = ? AND " + DBHelper.COLUMN_A_IS_ACTIVE + " = ? ";
    //final String whereArgs[] = {String.valueOf(mJournal),isActive};

    //Cursor cursor = database.query(DBHelper.TABLE_ABONE, kolonlar, whereClause, whereArgs,
      //      null, null, DBHelper.COLUMN_A_ID + " ASC");

    Cursor cursor = database.rawQuery("select * from " + DBHelper.TABLE_ABONE + " WHERE "
                          + DBHelper.COLUMN_A_DATE_END + " LIKE '%" + mDate + "%'", null);

    //Cursor cursor = database.query(DBHelper.TABLE_ABONE, kolonlar, null, null, null, null, DBHelper.COLUMN_A_NAME + " ASC");
    while (cursor.moveToNext()) {
        food = new AboneDataList();
        food.setId(cursor.getInt(cursor.getColumnIndex(DBHelper.COLUMN_A_ID)));
        food.setAbone_no(cursor.getInt(cursor.getColumnIndex(DBHelper.COLUMN_A_ABONE_NO)));
        food.setName(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_NAME)));
        food.setIs_stuff(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_IS_STUFF)));
        food.setCountry(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_COUNTRY)));
        food.setCity(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_CITY)));
        food.setTown(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_TOWN)));
        food.setAddress(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_ADDRESS)));
        food.setPhone(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_PHONE)));
        food.setMobile(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_MOBILE)));
        food.setDate_start(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_DATE_START)));
        food.setDate_end(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_DATE_END)));
        food.setCount(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_COUNT)));
        food.setIs_active(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_IS_ACTIVE)));
        food.setCargo(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_CARGO)));
        food.setYurt_id(cursor.getInt(cursor.getColumnIndex(DBHelper.COLUMN_A_YURT_ID)));
        food.setJournal(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_JOURNAL)));
        food.setMail(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_MAIL)));
        food.setBusiness(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_BUSINESS)));
        food.setBirth(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_BIRTH)));
        food.setGender(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_GENDER)));
        food.setAbone_type(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_ABONE_TYPE)));
        food.setMedeni_hal(cursor.getString(cursor.getColumnIndex(DBHelper.COLUMN_A_MEDENI_TYPE)));
        foodList.add(food);
    }
    database.close();
    cursor.close();
    return foodList;
}

score 0 · Answer 7 · edited Jul 04 '21 at 04:57

0

public Cursor fetchNamesByConstraint(String filter) {

    mDb.query(true, DATABASE_NAMES_TABLE, new String[] { KEY_ROWID,
            KEY_NAME }, KEY_NAME + " LIKE ?",
            new String[] {"%"+ filter +"%"}, null, null, null,
            null);

    return mCursor;
}

edited Jul 04 '21 at 04:57

Salahuddin Ahmed

4,854
4
14
35

answered Jul 03 '21 at 02:21

Xim John

21
3

score 0 · Answer 8 · answered Apr 04 '23 at 05:48

public ArrayList<ListingStoreModel> searchListing(String kpName, String psu){
    SQLiteDatabase db= this.getReadableDatabase();
    Cursor cursorCourses= db.rawQuery("SELECT * FROM LISTINGDATABASETABLE WHERE listingKhanaProdhanName LIKE '%"+kpName+"%' AND listingPSUNo = ?", new String[]{psu}, null);
    if(cursorCourses.moveToFirst()){
    -----------------------------
    ----------------------------- 
    }

Android SQLiteDatabase query with LIKE

8 Answers8

Linked