meta_query should check for wildcards when using LIKE operator

[ejdanderson]

This seems intentional, but when using the LIKE operator in a meta query, the % wildcards are automatically added to the start and end of the value when generating the SQL.

I think a more robust way of doing this would be to only add said wildcards if one cannot be found within the value itself.

I'm on the fence on this one. I don't think we expose MySQL's '%' wildcards anywhere ATM.

Replying to scribu:

I'm on the fence on this one. I don't think we expose MySQL's '%' wildcards anywhere ATM.

I dont think so either, there are a few places in the code that act in a similar way get_terms() for example, although in that case its a search parameter and seems appropriate.

I can see why exposing this is a bad idea, we don't want user input to be able to set SQL wildcards.

There have been numerous times though when I need to filter in custom SQL for a LIKE operator. A large majority of these have been to remove the leading '%'. Maybe a better solution is a flag to describe where '%' should be set - preceding, trailing, or both.

While I appreciate the need to abstract the SQL away from the query, as ejdanderson suggests a flag to control this would indeed be the appropriate means of handling this. As a stopgap measure, though, a filter on get_meta_sql to modify the WHERE clause should hopefully address this (example code provided below to remove the first % from the LIKE condition):

function remove_sql_wildcard_prefix($q)
{
        $q['where'] = preg_replace("/(LIKE ')%(.*?%')/", "$1$2", $q['where']);
        return $q;
}
add_filter('get_meta_sql', 'remove_sql_wildcard_prefix');

(disclaimer: the provided code is not yet tested, but in theory I think it works)

#23919 was marked as a duplicate.