Android Sqlitedatabase Query with Like

Android SQLiteDatabase query with LIKE

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.

Android SQLite select * from table where name like %key% using prepared statements

    if (name.length() != 0) {

        name = "%" + name + "%";
    }
    if (email.length() != 0) {
        email = "%" + email + "%";
    }
    if (Phone.length() != 0) {
        Phone = "%" + Phone + "%";
    }
    String selectQuery = " select * from tbl_Customer where Customer_Name like  '"
            + name
            + "' or Customer_Email like '"
            + email
            + "' or Customer_Phone like '"
            + Phone
            + "' ORDER BY Customer_Id DESC";

    Cursor cursor = mDb.rawQuery(selectQuery, null);`

Android SQLite Like query

The % wildcards must be part of the string:

... + " LIKE '%" + strname+ "%'", ...

Please note that this code will blow up when the string contains a quote.
You should use parameters instead:

db.query(true, DATABASE_TABLE,
         new String[] {KEY_ROWID,KEY_FAULTREF,KEY_WORKNUM,KEY_ORIGNUM,KEY_REPBY,KEY_REPDATTIM,KEY_DESCRIPTION},
         KEY_REPBY + " LIKE ?",
         new String[] { "%" + strname + "%" },
         null, null, null, null);

like operator syntax in sqlite with android

use this way:

Cursor c = sampleDB.rawQuery("SELECT FirstName, Age FROM " +
SAMPLE_TABLE_NAME + " where " +field+ " like '%"+search+"%'" , null);

---

Edited: create function

public Cursor getSearch(String SAMPLE_TABLE_NAME, String field,
            String search) {
        SQLiteDatabase sampleDB = this.getReadableDatabase();
        Cursor c = sampleDB.rawQuery("SELECT FirstName, Age FROM "
                + SAMPLE_TABLE_NAME + " where " + field + " like '%" + search
                + "%'", null);
        return c;
    }

Using LIKE and LIMIT in sqlite queries in Android

WARNING: You should NOT use string concatenation with the + operator to insert user input in a SQL query.This leaves your app open to a SQL injection attack. I cannot emphasize this enough. Mitigating this common security flaw should be a top priority for all database developers.

Instead, you should use the ? place holder syntax:

String query = "SELECT " + CITIES_NAME +
                    " FROM " + TABLE_CITIES +
                    " WHERE " + CITIES_NAME +
                    " LIKE ?" +
                    " LIMIT 8";
String[] args = {nameLetters + "%%"};
Cursor cursor = database.rawQuery(query, args);

Even if the database is small and only used for your individual app, it is best to make this syntax a habit. Then when you work on larger, more critical databases, you won't have to worry about this issue as much.

This also has the advantage that it quotes the input for you. You completely avoid the error which you encountered that prompted the original question.

Cannot use Like statement in Android SQLite with argument and % character

Use || to concat parameter and wildcard:

SELECT * FROM config WHERE Directory Like ? || '%';

SQLite: Select using LIKE '%?%' with rawQuery

The Problem is with the SQL query you have used.

you are giving ? A String which is not acceptable for prepare statements.
select distinct * from table_name where X like '%?%'; is not correct because ? will be a string with double quotation inside a quotation like '%"your_string"%'.

instead write:

select distinct * from table_name where X like ?;

and for ? use "'%your_string%'". you can apply this to your array of string too.

like query in sqlite android not working properly

I found the problem..thanks to M D

i changed the way of using my dsplay cursor into this

 if(display.getCount()>0) {
                display.moveToFirst();
                do {
                    array.add(display.getString(1));
                } while (display.moveToNext());
            }
            else{
                //name not found case..
            }

sqlite LIKE problem in android

I think you shouldn't use selArgs for LIKE such a way. You may try this:

Cursor cursor = m_db.query(MY_TABLE, new String[] {"rowid","Word"},"Word"+" LIKE '"+name+"%'", null, null, null, null);

EDIT:

OK, if you want be safe from SQL injections, don't use above solution, use this:

Cursor cursor = m_db.query(MY_TABLE, new String[] {"rowid","Word"},"Word LIKE '?'", new String[]{name+"%"}, null, null, null);

SQLiteDatabase.query method

tableColumns

• null for all columns as in SELECT * FROM ...
• new String[] { "column1", "column2", ... } for specific columns as in SELECT column1, column2 FROM ... - you can also put complex expressions here:
  
  new String[] { "(SELECT max(column1) FROM table1) AS max" } would give you a column named max holding the max value of column1

whereClause

• the part you put after WHERE without that keyword, e.g. "column1 > 5"
• should include ? for things that are dynamic, e.g. "column1=?" -> see whereArgs

whereArgs

• specify the content that fills each ? in whereClause in the order they appear

the others

• just like whereClause the statement after the keyword or null if you don't use it.

Example

String[] tableColumns = new String[] {
    "column1",
    "(SELECT max(column1) FROM table2) AS max"
};
String whereClause = "column1 = ? OR column1 = ?";
String[] whereArgs = new String[] {
    "value1",
    "value2"
};
String orderBy = "column1";
Cursor c = sqLiteDatabase.query("table1", tableColumns, whereClause, whereArgs,
        null, null, orderBy);

// since we have a named column we can do
int idx = c.getColumnIndex("max");

is equivalent to the following raw query

String queryString =
    "SELECT column1, (SELECT max(column1) FROM table1) AS max FROM table1 " +
    "WHERE column1 = ? OR column1 = ? ORDER BY column1";
sqLiteDatabase.rawQuery(queryString, whereArgs);

---

By using the Where/Bind -Args version you get automatically escaped values and you don't have to worry if input-data contains '.

Unsafe: String whereClause = "column1='" + value + "'";
Safe: String whereClause = "column1=?";

because if value contains a ' your statement either breaks and you get exceptions or does unintended things, for example value = "XYZ'; DROP TABLE table1;--" might even drop your table since the statement would become two statements and a comment:

SELECT * FROM table1 where column1='XYZ'; DROP TABLE table1;--'

using the args version XYZ'; DROP TABLE table1;-- would be escaped to 'XYZ''; DROP TABLE table1;--' and would only be treated as a value. Even if the ' is not intended to do bad things it is still quite common that people have it in their names or use it in texts, filenames, passwords etc. So always use the args version. (It is okay to build int and other primitives directly into whereClause though)

---

Related Topics