Using parameter tokens in IFNULL function in SQLite

Question

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!

forpas · Accepted Answer · 2021-08-05 12:34:45Z

1

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

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

answered Aug 5, 2021 at 12:34

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using parameter tokens in IFNULL function in SQLite

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related