Using a regexp in a MySQL query

Published on 2020-01-18 • Modified on 2020-01-18

In this snippet we will see how to use a regexp to select data in a MySQL query. First in the where clause we declare the column to use then the REGEXP keyword followed by the pattern. In this example, we retrieve articles starting by a "D" followed by at least a number. Run the snippet to check if the retrieved articles match this rule.


<?php declare(strict_types=1);

// src/Controller/Snippet/Snippet71Trait.php

namespace App\Controller\Snippet;

use Doctrine\DBAL\Connection;
use Symfony\Component\Config\Definition\Exception\InvalidTypeException;

/**
 * I am using a PHP trait to isolate each snippet in a file.
 * This code should be called from a Symfony controller extending AbstractController (as of Symfony 4.2)
 * or Symfony\Bundle\FrameworkBundle\Controller\Controller (Symfony <= 4.1).
 * Services are injected in the main controller constructor.
 */
trait Snippet71Trait
{
    public function snippet71(): void
    {
        if (!$this->connection instanceof Connection) {
            throw new InvalidTypeException('Doctrine connection is invalid.');
        }

        $sql = "SELECT name FROM article WHERE name REGEXP '^D.*[1-9]'";
        echo $sql.PHP_EOL.PHP_EOL;

        var_dump(array_column($this->connection->fetchAll($sql), 'name'));

        // That's it! 😁
    }
}

 Run this snippet  ≪ this.showUnitTest ? this.trans.hide_unit_test : this.trans.show_unit_test ≫  More on Stackoverflow   Read the doc  Random snippet

<?php declare(strict_types=1);

namespace App\Tests\Controller\Snippets;

use Doctrine\DBAL\Connection;
use Symfony\Bundle\FrameworkBundle\Test\KernelTestCase;

/**
 * @covers Snippet71Trait
 */
final class Snippet71Test extends KernelTestCase
{
    private Connection $connection;

    protected function setUp(): void
    {
        self::bootKernel();
        $this->connection = self::$kernel->getContainer()->get('doctrine.dbal.default_connection');
    }

    /**
     * @covers Snippet71Trait::snippet71
     */
    public function testSnippet71(): void
    {
        $results = $this->connection->fetchAll("SELECT name FROM article WHERE name REGEXP '^D.*[1-9]'");
        $phpRegexp = '/D.*[1-9]/';
        foreach (array_column($results, 'name') as $name) {
            self::assertMatchesRegularExpression($phpRegexp, $name);
        }

        // article that doesn't match
        $results = $this->connection->fetchAssoc('SELECT name FROM article WHERE id = 1');
        self::assertDoesNotMatchRegularExpression($phpRegexp, $results['name']);
    }
}