CODEWITHSUNDEEP
javascriptnode.jssqliteelectron
SRR
SRR

Reputation: 1758

Run LIKE query with optional parameters - SQLite3

I'm creating an electron application that uses better-sqlite3 and I wanted to know, in general, if there was a way to run a query that uses LIKE with optional parameters? Let's say there's a table people that contains the columns firstname and lastname. A user could search by:

Firstname only in which case the query would be:

SELECT * from people WHERE firstname LIKE %userEnteredFname%

Lastname only in which case the query would be:

SELECT * from people WHERE lastname LIKE %userEnteredLname%

Or both fields:

SELECT * from people WHERE firstname LIKE %userEnteredFname% AND lastname LIKE %userEnterLname%

And just FYI, there's a way to INSERT with optional parameters:

const stmt = db.prepare('INSERT INTO cats (name, age) VALUES (?, ?)');
const info = stmt.run('Joey'); //age would be null

The query depends on which search parameter is entered. Sure enough you can prepare statements for a small number of input fields but I have about 7 and there can be a lot of combinations there.

Upvotes: 0

Views: 619

Answers (2)

forpas
forpas

Reputation: 164139

The operator LIKE returns NULL when any of its operands is NULL.
So for all the columns use COALESCE() which will return 1 (= TRUE) in case the value passed is NULL:

SELECT * 
FROM people 
WHERE COALESCE(firstname LIKE '%' || ? || '%', 1) 
  AND COALESCE(lastname LIKE '%' || ? || '%', 1)

The ? placeholders stand for the parameters that you will pass to the query.

Upvotes: 2

avt613
avt613

Reputation: 309

As long as userEnterLname is an empty string,

 SELECT * from people WHERE firstname LIKE %userEnteredFname% AND lastname LIKE %userEnterLname%

is the same as

 SELECT * from people WHERE lastname LIKE %userEnteredLname%

So just keep the extra variables empty unless you want to search them.

Upvotes: 1

Related Questions