序列化数据中的正则表达式

Question

I am looking to a database search on serialized data. I am currently using Symfony2 as my Framework making pdo_mysql calls using Doctrine 2. What I would like to do is create a query that uses REGEXP to find data within a certian part of the array. The data I am trying to search within looks like this: -

a:1:{s:8:"bedrooms";a:5:{i:0;i:1;i:1;i:2;i:2;i:3;i:3;i:4;i:4;s:2:"5+";}}

So let's say I am looking for a record that has 3 bedrooms, then I would want it to find: -

i:2;i:3

The query I have come up with so far is: -

SELECT * FROM table WHERE field_name REGEXP '.*"bedrooms"; a:[0-9]+:{i:[0-9]+;i:3;}.*';

However this doesn't work. Can someone help me find a fix around this please? I think it's down to the way the regular expression is written.

Also its worth noting that there are other arrays stored in the field such credit limits and other data.

Thank you in advance.

Answer 1

I believe you can do it with the help of negated character class [^{}] that matches any character but a { and }:

.*"bedrooms";a:[0-9]+:[{][^{}]*i:[0-9]+;i:3[^{}]*[}]

See the regex demo