Search code examples
dartfluttersqflite

How to query the sqflite database using the LIKE statement and the % or * sql wildcard


I'm trying to query a Companies table using the companyName, I want to get the companies whose companyName(s) contains a query string, not only the companies whose companyName are exact matches of my query strings. If I was using sql, I would use the ---WHERE companyName LIKE %queryString% statement. So my question is how do I make such a query in sqflite? Right now I am getting a syntax error because of the %

Table Structure

CREATE TABLE Companies(
        id INTEGER PRIMARY KEY, 
        name TEXT,
        email TEXT, 
        website TEXT, 
        about TEXT, 
        phone TEXT,
        logo TEXT,
        created_at TEXT
      );
 );

Code for searching

Future<List<CompanyModel>> filterCompanies(String filterCriteria) async {
final db =await database;
List<CompanyModel> filteredCompanies = [];

var res = await db.rawQuery("SELECT * FROM Companies WHERE name LIKE %?%;", [filterCriteria]);
if(res.length !=null){
  for (var item in res){
    filteredCompanies.add(JobModel.fromDb(item));
  }

 } 
 return filteredCompanies;
}

When I add the %, I get a syntax error.


Solution

  • The accepted answer leads to build error for me. I'm using sqflite: ^1.1.7+1 with flutter: v1.13.5.
    From the discussion below, it comes out the percent character should be in the parameter instead of in the query, i.e.,

     res = await db.query(
      "Companies",
      where: "name LIKE ?"
      whereArgs: ['%$title']
     );
    

    Ref: https://github.com/tekartik/sqflite/issues/316