Using parameter tokens in IFNULL function in SQLite

user_10642923 0
Viewed 26

I am implementing a search function for my database. For some search terms, the input might be null, so I'm using SQLite's ifnull function. Obviously, users might not input the perfect name, so I'm using LIKE to lookup (The other search terms are omitted here). However, it seems that I'm using the wrong syntax.

This is my query:

args = (nameVar)
cur.execute("SELECT * FROM wines WHERE name LIKE IFNULL('%?%',name) COLLATE NOCASE", args)

This returns no results. However, if I instead use the python variables to build the string instead of using? for the parameters, it works perfectly.

cur.execute("SELECT * FROM wines WHERE name LIKE IFNULL('%"+nameVar+"%',name) COLLATE NOCASE")

This, however, is not a viable solution, as nameVar might be null which gives a string builder error. Any help is appreciated thank you!

api sqlite python
1 Answers

You can use SQLite's concatenation operator || to concatenate the value of the ? placeholder:

SELECT * 
FROM wines 
WHERE name 
LIKE IFNULL('%' || ? || '%', name)
user_104988280
Related