8

I have a custom WP_Query with the following meta_query:

$meta_query = array();

$meta_query['relation'] = 'AND';

if(!empty($postcode)) {
    $meta_query[] = array(
        'key' => 'postcode',
        'value' => $postcode,
        'compare' => 'LIKE'
    );
}               

if(!empty($email)) {
    $meta_query[] = array(
        'key' => 'email_address',
        'value' => $email,
        'compare' => 'LIKE'
    );
}

The problem with this is WordPress is wrapping each meta value in %% to use as part of the LIKE comparison in the query e.g WHERE meta_value LIKE '%hello@example.com%'

Is it possible to set up a meta_query so only one percentage sign is used, so we can check if a value starts with or ends with a phrase? e.g WHERE meta_value LIKE 'hello@%'

  • Keep in mind post meta queries are super expensive and slow, if you need to filter search or query data, it should be stored using a taxonomy, not post meta – Tom J Nowell Dec 21 '17 at 18:11
16

You could try the REGEXP version:

    'meta_query' => array(
        array(
            'key'       => 'email_address',
            'value'     => '^hello@',
            'compare'   => 'REGEXP',
        )
    )

where ^ matches the beginning of a string.

You can check the MYSQL reference on REGEXP here for more info.

Notice that these are the possible values of the meta compare parameter:

'=', '!=', '>', '>=', '<', '<=', 
'LIKE', 'NOT LIKE','IN', 'NOT IN', 
'BETWEEN', 'NOT BETWEEN', 'NOT EXISTS', 
'REGEXP', 'NOT REGEXP', 'RLIKE'

according to the WordPress 3.9.2 source.

From the MYSQL ref:

Name        Description
------------------------------------------------------
NOT REGEXP  Negation of REGEXP
REGEXP      Pattern matching using regular expressions
RLIKE       Synonym for REGEXP
| improve this answer | |
  • 1
    Great stuff, thanks very much. It's a shame they don't list REGEXP as a valid compare value in the docs (From codex.wordpress.org/Class_Reference/WP_Meta_Query Possible values are '=', '!=', '>', '>=', '<', '<=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', 'EXISTS' (only in WP >= 3.5), and 'NOT EXISTS' (also only in WP >= 3.5). Default value is '='.) – Andy Aug 28 '14 at 13:22
  • 2
    I updated the Codex with these extra parameters: REGEXP, NOT REGEXP and RLIKE. – birgire Aug 28 '14 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.