Sql Like Query: Escape Wildcards

I have a LIKE query I'm running on a column of Android Uri which are formatted like such: content://com.android.externalstorage.documents/tree/0000-0000%3A_Issues/document/0000-00

Solution 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.

Solution 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
 */publicstaticString 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(newString[filter.hiddenFolders.size()]),
                    selectionArgs,
                    AND,    // Requires AND so multiple hides don't negate each othertrue,   // NOTnull,   // No wild to start, matches path exactly"%",    // Wildcard end to match all children"%"));  // Uri contain '%' which means match any so escape them