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

Question

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.

Devora

Where is your code ? What is your db structure ? What have you found in the existing documentation that is relevant to your issue ? – Leake

The existing documentation says nothing on the subject. I found nothing on the issue when I searched for ways of doing it. I edited the post with my code – Devora

Answer 1

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

Reeva

Answer 2

I tried all of the answers above but not worked. Finally:

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

Otolaryngology

This one is a working solution for me, thanks! – Overstreet

Answer 3

The below code works:

var res = await db.rawQuery("SELECT * FROM Companies WHERE name LIKE '%$filterCriteria%'");

Achaea

I guess the answer is , you need to put % on both sides of the LIKE variable – External

Answer 4

This is the code that ended up working perfectly for me.

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

Devora

Answer 5

You have to use this syntax : LIKE " searched string "

Here is an example :

var res = await db.rawQuery(' SELECT * FROM Companies WHERE name LIKE "%$filterCriteria%" ');

It works with flutter and sqflite

Kleenex

Answer 6

Use this

var res = await db.rawQuery("SELECT * FROM Companies WHERE name LIKE '%?%';", [filterCriteria]);

Instead of

var res = await db.rawQuery("SELECT * FROM Companies WHERE name LIKE %?%;", [filterCriteria]);

put like condition in single quote('...')

Honaker

Answer 7

this works for me

var res = await db.rawQuery("SELECT * FROM Companies WHERE name LIKE ?",['%$filterCriteria%']);

Highhanded

Hello! While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply. – Ofelia