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.

CodePudding user response：

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

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

• see
  • 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.