Syntax for MySQL REGEXP CONCAT

Question

I'm ultimately trying to make a PDO prepared statement using the REGEXP CONCAT function. In phpmyadmin I can run the following query which returns proper results for my database contents (the 'city' value (Denver, in this case)) will eventually be the bound parameter, but I'm putting it in there explicitly now for development):

SELECT `user_ID` as `ID` 
FROM `usermeta` 
WHERE (`meta_key` = 'custom_field')
AND (`meta_value` REGEXP '.*"leagues";.*s:[0-9]+:"A".*')
AND (`meta_value` REGEXP '.*"city";.*s:[0-9]+:"Denver".*')

However, if I try to use the CONCAT function on the last line, the result is an empty set. In other words, MySQL isn't throwing any errors on the query itself, but the correct data isn't being selected:

SELECT `user_ID` as `ID` 
FROM `usermeta` 
WHERE (`meta_key` = 'custom_field')
AND (`meta_value` REGEXP '.*"leagues";.*s:[0-9]+:"A".*')
AND (`meta_value` REGEXP CONCAT('\'.*"city";.*s:[0-9]+:"', 'Denver', '".*\''))

I've tried escaping the colon, semicolon and period characters with no luck. Any help is very much appreciated.

done any basic debugging, like select concat(...) to see the pattern you are generating? — Marc B
– Marc B, Commented Sep 7, 2016 at 14:26
Marc B: Yes, when I send just the select concat statement I get this: '.*"city";.*s:[0-9]+:"Denver".*' I'm just trying to get the concat function working correctly. In the end, 'Denver' will be replaced with a PDO parameter (? or :city). — user1596165
– user1596165, Commented Sep 7, 2016 at 14:28

chris85 · Accepted Answer · 2016-09-07 15:40:17Z

1

You are adding literal quotes to your expression:

AND (`meta_value` REGEXP CONCAT('\'.*"city";.*s:[0-9]+:"', 'Denver', '".*\''))    
                                  ^                                       ^

Those aren't in your first expression. The quotes in the first expression are encapsulating the SQL string. So:

AND (`meta_value` REGEXP CONCAT('.*"city";.*s:[0-9]+:"', 'Denver', '".*'))

I think would work. You also don't need to use the mysql concat. You could just concatenate the variable into the expression in the binding. Make the SQL:

SELECT `user_ID` as `ID` 
FROM `usermeta` 
WHERE (`meta_key` = 'custom_field')
AND (`meta_value` REGEXP '.*"leagues";.*s:[0-9]+:"A".*')
AND (`meta_value` REGEXP ?)

Then build the binding like:

'.*"city";.*s:[0-9]+:"' . $city . '".*'

The leading and trailing .* are also not necessary. The rule will match without that because you aren't using anchors.

edited Sep 7, 2016 at 15:40

answered Sep 7, 2016 at 15:12

chris85

23.9k7 gold badges36 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1596165 Over a year ago

Thanks, Chris. You were right about the quotes. I'm using the concat function because I was under the impression that placeholders couldn't be enclosed in quotes. (stackoverflow.com/a/39171920/1596165) In production, the statement would be like concat('.*"city";.*s:[0-9]+:"', ?, '".*') Do you agree with this?

chris85 Over a year ago

You can not quote the placeholder, that is correct. You can concatenate at the PHP level though where you tell the PDO what to bind. Bind the whole expression with the value and it should work. Note the last query, AND (`meta_value` REGEXP ?) that is how the placeholder should be written. Then you bind '.*"city";.*s:[0-9]+:"' . $city . '".*' you could do execute(array('.*"city";.*s:[0-9]+:"' . $city . '".*')) or use one of the other binding methods.

Collectives™ on Stack Overflow

Syntax for MySQL REGEXP CONCAT

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related