Numeric only in MySQL (and Doctrine' QueryBuilder)

Published on July 03, 2014

Problem

I have an old project which uses a varchar type to store a phonenumber. But now I want to be able to query these phonenumbers, on their numeric value. There is no native function to compare against a numeric value of a specific column in MySQL, neither in Doctrine’ QueryBuilder.

Solution

I want to be able to do something like this, in a native MySQL query:

SELECT id FROM users WHERE NUMERIC_ONLY(phone) LIKE '%0474%'

Implementation

MySQL functions to the resque

Create function that checks if a specific string is numeric.

1
CREATE FUNCTION IS_NUMERIC (val varchar(255)) RETURNS tinyint RETURN val REGEXP '^-?[0-9]+$'

Then we can re-use this function, to get the numeric value from a varchar. Loop over every character and only return the numeric values.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE FUNCTION NUMERIC_ONLY (val VARCHAR(255))
 RETURNS VARCHAR(255)
BEGIN
 DECLARE idx INT DEFAULT 0;
 IF ISNULL(val) THEN RETURN NULL; END IF;

 IF LENGTH(val) = 0 THEN RETURN ""; END IF;

 SET idx = LENGTH(val);
  WHILE idx > 0 DO
  IF IS_NUMERIC(SUBSTRING(val,idx,1)) = 0 THEN
   SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
   SET idx = LENGTH(val)+1;
  END IF;
  SET idx = idx - 1;
  END WHILE;
  RETURN val;
 END

Create a PHP function

We can implement this in a PHP function, and call it whenever we need this custom MySQL function in a native query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
public function addNumericOnlyFunctionToMySQL()
{
    $queries = array();
    $queries[] = "DROP FUNCTION IF EXISTS IS_NUMERIC";
    $queries[] = "DROP FUNCTION IF EXISTS NUMERIC_ONLY";
    $queries[] = "CREATE FUNCTION IS_NUMERIC (val varchar(255)) RETURNS tinyint RETURN val REGEXP '^-?[0-9]+$'";
    $queries[] = "
        CREATE FUNCTION NUMERIC_ONLY (val VARCHAR(255))
         RETURNS VARCHAR(255)
        BEGIN
         DECLARE idx INT DEFAULT 0;
         IF ISNULL(val) THEN RETURN NULL; END IF;

         IF LENGTH(val) = 0 THEN RETURN ""; END IF;

         SET idx = LENGTH(val);
          WHILE idx > 0 DO
          IF IS_NUMERIC(SUBSTRING(val,idx,1)) = 0 THEN
           SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
           SET idx = LENGTH(val)+1;
          END IF;
          SET idx = idx - 1;
          END WHILE;
          RETURN val;
         END
    ";

    foreach($queries as $query) {
        $this->getEntityManager()->getConnection()->exec($query);
    }
}

Now we can use this in native MySQL, but I want to use it within the Doctrine Querybuilder.

Register Custom DQL Function

<?php
namespace YourProject\DoctrineExtensions\Query;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;

class NumericOnly extends FunctionNode
{
    public $numberExpression = null;

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->numberExpression = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'NUMERIC_ONLY(' .$this->numberExpression->dispatch($sqlWalker) . ')';
    }
}

$config = new \Doctrine\ORM\Configuration();
..
$config->addCustomStringFunction('NUMERIC_ONLY', 'YourProject\DoctrineExtensions\Query\NumericOnly');

Thanks for reading

Feel free to leave a comment if you have remarks or like this post

Joeri Verdeyen