在查询中使用正则表达式时MySQL查询异常

Question

PHP (Symfony 2.0):

$term = "3";

$query = $em->createQuery(
     "SELECT * FROM SoftsystemSupportBundle:Shop WHERE sid REGEXP '^$term'"
);

$shops = $query->getResult();    

Throws an exception:

Error: Expected IdentificationVariable | StateFieldPathExpression | AggregateExpression | \"(\" Subselect \")\" | ScalarExpression, got '*'"

I want to get all entities where sid field matches regex.

Any ideas whats wrong?

Answer 1

You are making a misuse of the QueryBuilder object returned by the CreateQuery() method.

You are writing DQL (Doctrine Query Language) and not MySQL. REGEXP is not a DQL keyword, hence the error.

You have the same issue of this post: Using REGEXP in Doctrine 2.X ORM.

A solution is given there.