Meta query with string starting like pattern

Question

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 '%[email protected]%'

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@%'

Answer 1

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