SQLite bind parameter to wildcard

Question

I've created a query to delete rows from my table if they match some criteria.

sqlite3_prepare(sqlite->db, "DELETE FROM test WHERE field1=? AND field2=? AND field3=? AND field4=? AND field5=?", -1, &sqlite->deleteSymbol, 0);

I then bind them to NULL terminated strings. However, if any of the strings passed by the caller of my function is NULL, then this means that any value for the given column should match.

I.e., if field1, field3 and field5 are NULL, then the query would be equivalent to

DELETE FROM test WHERE field2=? AND field4=?

Can this be achieved while reusing the output of sqlite3_prepare, without creating separate queries for each combination of NULL/non-NULL strings?

You could trying using `LIKE` from this post if you are using it like a `=` without a wildcard there should be no performance hit: http://stackoverflow.com/questions/6142235/sql-like-vs-performance and just in the case of `NULL` you would use a wildcard. There are potential performance issues in the `NULL` case but that will be specific to your DB. — Shafik Yaghmour, Apr 16 '13 at 12:38

score 1 · Accepted Answer · answered Apr 16 '13 at 13:17

A parameter always replaces one specific value, or NULL. What you want to do is to change the semantics of the = operator, but this is not possible by changing one of its operand values.

Just prepare a new DELETE command. There is no big overhead in preparing a simple statement like this.

SQLite bind parameter to wildcard

1 Answers1