Search code examples
sqlsqlitefull-text-searchfts4

Using MATCH with AND/OR operators with parameters doesn't work in SQLite

I'm having problem with executing select statement while using match and and/or operators while using parameters.

The statement looks like this:

SELECT ColumnName1, ColumnName2 
FROM TableName 
WHERE TableName MATCH 'ColumnName1:@param1 AND ColumnName2:@param2';

But when I change @param1 and @param2 with real values, for example like this

SELECT ColumnName1, ColumnName2 
FROM TableName 
WHERE TableName MATCH 'ColumnName1:Value AND ColumnName2:Value';

it works fine.

I guess the problem is those single quotes around my AND statement. Is there any way to avoid them but still be able to use and/or operators or is problem somewhere else?

Solution

  • You can't use SQL parameters to replace parts of an argument, but perhaps you may be able to use string concatenation (I didn't test this):

    ... MATCH 'ColumnName1:' || @param1 || ' AND ColumnName2:' || @param2;

    Else you'll have to go the canonical way, build the string in your application and provide it as a single parameter:

    ... MATCH @param;