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.

Where is your code ? What is your db structure ? What have you found in the existing documentation that is relevant to your issue ? — LoneWanderer, Apr 29 '19 at 15:01
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 — Diligence Vagere, May 02 '19 at 08:53

score 19 · Accepted Answer · answered Jan 21 '20 at 17:32

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

score 10 · Answer 2 · answered Jan 01 '21 at 06:06

10

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

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

answered Jan 01 '21 at 06:06

Pham Tung

181
1
6

This one is a working solution for me, thanks! – evanca May 11 '21 at 13:40

Munjata KETA · Answer 3 · 2021-12-29T11:27:46.153

5

The below code works:

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

edited Dec 29 '21 at 11:27

answered Jun 04 '19 at 21:28

Munjata KETA

109
1
5

I guess the answer is , you need to put % on both sides of the LIKE variable – Bhikkhu Subhuti Aug 13 '21 at 13:00

score 3 · Answer 4 · answered Jun 25 '19 at 12:27

3

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

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

answered Jun 25 '19 at 12:27

Diligence Vagere

178
1
3
9

score 0 · Answer 5 · answered Feb 12 '20 at 21:28

0

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

answered Feb 12 '20 at 21:28

Kachler Vincent

1

score 0 · Answer 6 · answered Feb 13 '20 at 09:54

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('...')

score 0 · Answer 7 · answered Apr 20 '20 at 22:14

0

this works for me

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

answered Apr 20 '20 at 22:14

gore chinoda

9
1

Hello! While this code may solve the question, [including an explanation](https://meta.stackexchange.com/q/114762) 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. – Brian61354270 Apr 21 '20 at 00:36

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

7 Answers7