如何在sqflite中使用LIKE语句及%/*通配符查询数据库？

Ah, I see the issue here! The problem with your current query is that you're trying to wrap the placeholder ? directly with % in the SQL string, which sqflite (and SQLite in general) doesn't accept as valid syntax. Let me break down how to fix this properly.

Method 1: Append % to your parameter value (Recommended) #

The safest and cleanest way is to add the wildcard characters to your filter criteria before passing it as a parameter. This also helps prevent SQL injection risks.

Modify your query line like this:

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

Method 2: Use SQLite's string concatenation operator || #

If you prefer to handle the wildcards directly in the SQL statement, you can use SQLite's || operator to concatenate the % with your placeholder:

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

Also, fix the model conversion bug! #

I noticed a typo in your code: you're trying to add a JobModel to a list of CompanyModel. Make sure to use CompanyModel.fromDb(item) instead to avoid type errors:

filteredCompanies.add(CompanyModel.fromDb(item));

Full corrected code example #

Here's the updated function with the recommended method (plus a minor improvement to check for non-empty results):

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.isNotEmpty) {
    for (var item in res) {
      filteredCompanies.add(CompanyModel.fromDb(item));
    }
  }
  return filteredCompanies;
}

Why your original code failed #

SQLite expects the placeholder ? to be a standalone token in the query string. Writing %?% confuses the parser because it doesn't recognize ? as a valid placeholder when it's wrapped between % characters. By either attaching the % to the parameter value or concatenating them in SQL, you're giving SQLite a valid expression to evaluate.

内容的提问来源于stack exchange，提问作者Diligence Vagere