SQL LIKE query: escape wildcards

Question

I have a LIKE query I'm running on a column of Android Uri which are formatted like such:

1. content://com.android.externalstorage.documents/tree/0000-0000%3A_Issues/document/0000-0000%3A_Issues%2FParentB
2. content://com.android.externalstorage.documents/tree/0000-0000%3A_Issues/document/0000-0000%3A_Issues%2FParentA
3. content://com.android.externalstorage.documents/tree/0000-0000%3A_Issues/document/0000-0000%3A_Issues%2FParentA%2FParentB

These correspond to this file tree:

_Issues
|-ParentA
   |-ParentB
|-ParentB

I have attempted all sorts of manual queries escaping the wildcards, but I can't seem to get a proper match. I simplified it down to this for testing:

select name from meta where parent LIKE '%_Issues%2FParentB%';

1. If I escape '%':

%_Issues\%2FParentB%'

I get no results at all.

2. If I don't escape:

'%_Issues%2FParentB%'

I match both the shallow ParentB (desired) and the nested ParentB (undesired). I understand that's because I'm allowing anything between Issues and ParentB with the %.

What I don't understand is why query 1 does has no results. What am I missing?

---

Update

select name from meta where parent LIKE '%_Issues_2FParentB%';

worked, note the '_', so the '\' was clearly not escaping this db. Explicitly stating the escape character as @GordonLinoff suggested worked on my computer:

select name from meta where parent LIKE '%_Issues\%2FParentB%' escape '\';

Now to figure our how to accomplish the same in Android...

Answer 1

Yes. The simplest way is to use =, if that is appropriate.

Otherwise, the LIKE syntax includes an escape character. In your example, $ doesn't appear anywhere, so you could do:

where x like replace(y, '%', '$%') escape '$'

I believe that in all databases, the default is \, so you could also do:

where x like replace(y, '%', '\%') 

However, I prefer using the escape keyword so the intention is really clear.

Answer 2

To expand on the answer by @GordonLinoff, here is specifically how I did it in Android since it's slightly more convoluted:

/***
 * Creates a LIKE selection statement with all of the given arguments
 * @param column column to select on
 * @param likes array of arguments to select on
 * @param selectionArgs out: formatted arguments to pass to query
 * @param joiner joiner between individual LIKE
 * @param NOT true to set NOT LIKE for all selection arguments
 * @param argStart set a wildcard before every selection argument
 * @param argEnd set a wildcard after every selection argument
 * @return selection statement to query
 */
public static String createLike(String column, String[] likes, List<String> selectionArgs,
                                String joiner, boolean NOT,
                                @Nullable String argStart, @Nullable String argEnd,
                                @Nullable String escapeChar)
{
    StringBuilder selection = new StringBuilder();
    for (int i = 0; i < likes.length; i++)
    {
        if (i > 0) selection.append(joiner);

        if (argStart == null)
            argStart = "";
        if (argEnd == null)
            argEnd = "";

        selection.append(column)
                .append(NOT ? " NOT LIKE ?" : " LIKE ?");

        if (escapeChar != null)
            selection.append(" ESCAPE '\\'");

        String argument = likes[i];
        if (escapeChar != null)
            argument = argument.replace(escapeChar, "\\" + escapeChar);
        argument = argStart + argument + argEnd;
        selectionArgs.add(argument);
    }

    return selection.toString();
}

and call with:

DbUtil.createLike(Meta.PARENT,
                    filter.hiddenFolders.toArray(new String[filter.hiddenFolders.size()]),
                    selectionArgs,
                    AND,    // Requires AND so multiple hides don't negate each other
                    true,   // NOT
                    null,   // No wild to start, matches path exactly
                    "%",    // Wildcard end to match all children
                    "%"));  // Uri contain '%' which means match any so escape them