How to do case-insensitive comparison in delete() method of SQLiteDatabase class Android

Question

I am making an Android app in Kotlin with SQLite, and I use the code below to delete a record with 'subject' column.

val rows = db.delete(TABLE_NAME, "subject=?", arrayOf(subject))

It works as intended, e.g. if I provide the subject string "Math", it deletes the record whose subject is "Math".

My question is, how can I delete a record of subject of "math" case-insenstively. I mean, either Math or math should delete the record.

I read somewhere that I can use "COLLATE NOCASE" in SQL statement as DELETE FROM TABLE_NAME WHERE subject = "math" COLLATE NOCASE;

But as I am using the delete() method of SQLiteDatabase class in Android, I wonder how I can implement the same case-insensitive comparison.

Answer 1

For normal case insensitivity you can use LIKE rather than = e.g.

 val rows = db.delete(TABLE_NAME, "subject LIKE ?", arrayOf(subject))

• see https://www.sqlite.org/lang_expr.html#the_like_glob_regexp_match_and_extract_operators for a more in-depth explanation and caveats etc.

The following demonstrates using native SQLite using an SQLite tool (Navicat in this case, other tools exist):-

DROP TABLE IF EXISTS example; /* just in case */
CREATE TABLE IF NOT EXISTS example (subject TEXT); /* create the table */
/* Add some testing data */
INSERT INTO example VALUES ('Math'),('MAth'),('math'),('mATH'),('something else');
SELECT * FROM example; /* output the original data (Result 1)*/
DELETE FROM example WHERE subject = 'math'; /* case sensitive delete*/
SELECT * FROM example; /* output the data after deletion (Result 2)*/
DELETE FROM example  WHERE subject LIKE 'math'; /* case insensitive deletion */
SELECT * FROM example; /* output the data after deletion (Result 3)*/
/* Cleanup Environment */
DROP TABLE IF EXISTS example;

Resulting in:-

• original data

• after case sensitive deletion (i.e. just math is deleted)

• after the case insensitive deletion (i.e. all rows bar something deleted)
  • would delete the math row if it hadn't been deleted previously

I read somewhere that I can use "COLLATE NOCASE" in SQL statement as DELETE FROM TABLE_NAME WHERE subject = "math" COLLATE NOCASE;

Then you could use:-

val rows = db.delete(TABLE_NAME, "subject=? COLLATE NOCASE", arrayOf(subject))

• Note that the documentation for the 2nd parameter of the delete method says:-

whereClause String: the optional WHERE clause to apply when deleting. Passing null will delete all rows.

• without the WHERE keyword itself

  • WHERE is added by the method, like FROM is added to the tablename (first parameter) and DELETE is added; when building the SQL that is executed).

• i.e. the entire clause (as shown above). The WHERE clause expects and expression which can be quite complex see link above and scroll to the top for what an expression can consist of.