Better-sqlite3: Like queries

[fab1an]

Is there a way to create a query like this Select id from sometable where name like '?%'

(search for a string starting with a parameter)

I keep getting an error about to many parameter being passed.

[JoshuaWise]

You have to use the string concatenation operator (||).

select id from sometable where name like (? || '%');

[fab1an]

Thank you!

[fab1an]

There is a problem with your solution. When concatenating the query indices for LIKE aren't used, which results into massive performance drops.

Please reopen.

CREATE TABLE kvstore(
    key text not null unique collate nocase, 
    value text not null
);
CREATE INDEX kvstore_keys on kvstore(key collate nocase);

> explain query plan select * from kvstore where key like 'prefix.%';

  0|0|0|SEARCH TABLE kvstore USING INDEX kvstore_keys (key>? AND key<?)

> explain query plan select * from kvstore where key like ('prefix' || '.%');

  0|0|0|SCAN TABLE kvstore USING INDEX kvstore_keys

[JoshuaWise]

You can just concatenate the strings in JavaScript:

var stmt = db.prepare('select * from kvstore where key like ?');
var row = stmt.get(String(prefix) + '.%');

Anyways, this isn't really a place for discussing SQLite optimizations

[fab1an]

Oh, yes of course, that somehow slipped past me. Thanks for taking the time.

[simeon9696]

You can just concatenate the strings in JavaScript:

var stmt = db.prepare('select * from kvstore where key like ?');
var row = stmt.get(String(prefix) + '.%');

Anyways, this isn't really a place for discussing SQLite optimizations

Can we use template strings to get in .prepare ?

You have to use the string concatenation operator (||).

select id from sometable where name like (? || '%');

The above suggestion did not work for me

[JoshuaWise]

@simeon9696 See https://github.com/JoshuaWise/better-sqlite3/issues/389 on why template strings aren't a good idea.

[simeon9696]

@JoshuaWise thanks! I understand now. I was just hoping to not have to type String(prefix) + '%' for each column in a query