SQFLite: How to make a "SELECT table WHERE LIKE '%a word%'" query-CodePudding

I am new to flutter and SQFlite, and I have multiple instances in my 'category' column, in some inserts I have two or more instances in the same value separated by ","

For example:

'INSERT INTO todos(title, entidad, categoria, emision) VALUES ("title1", "e1", "fire, water, sky", "emi1"');

In that example I inserted two values in the categories column

What i want is to code a query type "like" SQLite sentence, that contains the word i want.

I only have this code inside my dbhelper.dart, it works fine.

Future<List<todo>> categoriaIdiomas() async {
    final db = await initializeDB();

    //this is the query
    final List<Map<String, dynamic>> queryResult =
        await db.rawQuery('SELECT * FROM todos WHERE categoria=?', ['water']);

    Map<String, dynamic> result = {};

    for (var r in queryResult) {
      result.addAll(r);
    }

    return queryResult.map((e) => todo.fromMap(e)).toList();
  }

I would like to know how to make something like this in sqflite:

"SELECT todos WHERE categoria LIKE '%water%'"

CodePudding user response：

First, remove the spaces between the words in column categoria and then use the operator LIKE:

await db.rawQuery("SELECT * FROM todos WHERE ',' || categoria || ',' LIKE '%,' || ? || ',%'", ["water"]);