Joeri Verdeyen bio photo

Joeri Verdeyen

Web-engineer, cyclist, Nespresso lover, Strava pusher.

Twitter LinkedIn Instagram Github Stackoverflow Last.fm Strava

Numeric only in MySQL (and Doctrine' QueryBuilder)

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 CREATE FUNCTION NUMERIC_ONLY (val VARCHAR(255))
 2  RETURNS VARCHAR(255)
 3 BEGIN
 4  DECLARE idx INT DEFAULT 0;
 5  IF ISNULL(val) THEN RETURN NULL; END IF;
 6 
 7  IF LENGTH(val) = 0 THEN RETURN ""; END IF;
 8 
 9  SET idx = LENGTH(val);
10   WHILE idx > 0 DO
11   IF IS_NUMERIC(SUBSTRING(val,idx,1)) = 0 THEN
12    SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
13    SET idx = LENGTH(val)+1;
14   END IF;
15   SET idx = idx - 1;
16   END WHILE;
17   RETURN val;
18  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 public function addNumericOnlyFunctionToMySQL()
 2 {
 3     $queries = array();
 4     $queries[] = "DROP FUNCTION IF EXISTS IS_NUMERIC";
 5     $queries[] = "DROP FUNCTION IF EXISTS NUMERIC_ONLY";
 6     $queries[] = "CREATE FUNCTION IS_NUMERIC (val varchar(255)) RETURNS tinyint RETURN val REGEXP '^-?[0-9]+$'";
 7     $queries[] = "
 8         CREATE FUNCTION NUMERIC_ONLY (val VARCHAR(255))
 9          RETURNS VARCHAR(255)
10         BEGIN
11          DECLARE idx INT DEFAULT 0;
12          IF ISNULL(val) THEN RETURN NULL; END IF;
13 
14          IF LENGTH(val) = 0 THEN RETURN ""; END IF;
15 
16          SET idx = LENGTH(val);
17           WHILE idx > 0 DO
18           IF IS_NUMERIC(SUBSTRING(val,idx,1)) = 0 THEN
19            SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
20            SET idx = LENGTH(val)+1;
21           END IF;
22           SET idx = idx - 1;
23           END WHILE;
24           RETURN val;
25          END
26     ";
27 
28     foreach($queries as $query) {
29         $this->getEntityManager()->getConnection()->exec($query);
30     }
31 }

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