Sqlite string with percentage signs

[sarmong]

I am unable to make the following work:

    const string = sql`SELECT * FROM videos WHERE title LIKE '%${searchString}%'`;
    return await db.query(string);

If I insert a string like '%mystring%' it obviously works, but when passing a variable, I get SQLITE_RANGE: column index out of range".

I tried many combinations of escaping or not with singe or double quotes or escaping % itself. If I don't include quotes at all, the query fails because of syntax error on percent sign.

[ForbesLindesay]

This is a duplicate of #163. The placeholder in curly brackets doesn't get inlined into the string, it gets treated as a value by SQLite (or whatever db engine you use).

Your above example attempts to run the SQL

SELECT * FROM videos WHERE title LIKE '%?%'

while passing in the searchString as a value.

SQLite does not support values/placeholders in the middle of strings, so you need to either:

1. Create the full string in JavaScript and pass it in as a single value.
2. Construct the string in SQLite by concatenating string literals and values.

For your example, I would go with option 1, which looks like:

const string = sql`SELECT * FROM videos WHERE title LIKE ${`%${searchString}%`}`;
return await db.query(string);

You could perhaps write this more clearly as:

const pattern = `%${searchString}%`
const string = sql`SELECT * FROM videos WHERE title LIKE ${pattern}`;
return await db.query(string);

Either of those would run identical SQL queries that look like:

SELECT * FROM videos WHERE title LIKE ?

and would pass in the searchString with the % either side as a single value/parameter.

Option 2 would look like:

const string = sql`SELECT * FROM videos WHERE title LIKE '%' || ${searchString} || '%'`;
return await db.query(string);

where || is the "concatenate" operator in SQLite to join strings together.

Note how I never put the ${...} bit inside quotes, as this would cause the out of range error in SQLite