Having issues with LIKE operator in SQLLiteHelper class

Question

I am using SQLLiteHelper class to do the database calls in android. I have below code in which basically I am trying to find any instance of old comment and replace with new comment. If I remove single quotes after LIKE then I get this message.

(1) near "%": syntax error

If I keep the single quotes then I get following message. Not sure what I am doing wrong.

Too many bind arguments.  2 arguments were provided but the statement needs 1 arguments.

public int updateComment(String oldComment, String newComment)
    {
        ContentValues values = new ContentValues();
        values.put(CommentEntry.COLUMN_NAME_COMMENT, newComment);
String selection = CommentEntry.COLUMN_NAME_COMMENT + " LIKE '%?%'";
        String[] selectionArgs = new String[]{ oldComment };
        int count = database.update(CommentEntry.TABLE_NAME, values, selection, selectionArgs);
        return count;
    }

Answer 1

I did find a bug report from 2013 that might be related: Android 56062. If that's an issue, here, you can bypass update() and run the query directly without a convenience method.

In the meantime, there's a syntax problem with your query. Bind values with a LIKE clause are tricky. A ? in quotes is just a literal question mark.

WHERE foo LIKE '%?%'

The above is just a search for any foo containing a question mark:

WHERE foo LIKE ?

The above allows you to pass in the wildcards as part of the bind param: %sometext%.

I think that's a little ugly, though. If we know that we're always going to search with wildcards, I'd want to use concatenation:

WHERE foo LIKE '%' || ? || '%'

As an aside, I hope you realize that a query with leading and trailing wildcards can be very expensive.