get_users meta_query: REGEXP not working for matching new lines

Question

I have a meta field "my_meta_field" which may contain the following for these sample users:

User A

99999
559225
12519

User B

5020492
99999
125191

User C

194295
1199999

Newlines are \r\n or \n. This is on Mac for dev and Unix for server side so it is probably \n.

If the target number is 99999 then I should get a match for User A and B. Need to be careful not to match situations like User C where a number has the target inside "1199999"

Matching start of line, or \n, or \r\n at the beginning should take care of this.

$user = get_users( array(
    'meta_query' => array(
        array(
            'key' => 'my_meta_field',
            'value' => '(^|\\n|\\r\\n)99999',
            'compare' => 'REGEXP',
        ),
    ),
) );

Unfortunately this only matches User A correctly. It doesn't match User B. This regular expression works perfectly in tools such as https://regex101.com/.

I've tried different formats such as: (^|\n|\r\n)99999

Thank you in advance if anyone knows what's wrong here.

Answer 1

I found the problem and also fixed a bug in the regex.

Final code:

'meta_query' => array( array( 'key' => 'my_meta_field', 'value' => "(^|\n|\r\n)99999($|\n|\r\n)", 'compare' => 'REGEXP', ), ),

The problem here was Wordpress was adding extra backslashes on the special characters. By wrapping the expression in double quotes, this problem was solved.

Also added end of string/new line capturing on the tail end of the target value.