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);

namespace App\Controller\Snippet;

use Doctrine\DBAL\Connection;

/**
 * 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.
 *
 * @property Connection $connection
 */
trait Snippet71Trait
{
    public function snippet71(): void
    {
        $sql = "SELECT name FROM article WHERE name REGEXP '^D.*[1-9]'";
        echo $sql.PHP_EOL.PHP_EOL;

        var_dump(array_column($this->connection->fetchAllAssociative($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

  Work with me!

<?php

declare(strict_types=1);

namespace App\Tests\Integration\Controller\Snippets;

use App\Tests\WebTestCase;
use Doctrine\DBAL\Connection;

/**
 * @see Snippet71Trait
 */
final class Snippet71Test extends WebTestCase
{
    private Connection $connection;

    protected function setUp(): void
    {
        $this->connection = $this->getDoctrineDefaultConnection();
    }

    /**
     * @see Snippet71Trait::snippet71
     */
    public function testSnippet71(): void
    {
        $results = $this->connection->fetchAssociative("SELECT name FROM article WHERE name REGEXP '^D.*[1-9]'");
        $phpRegexp = '/D.*[1-9]/';
        if (!\is_array($results)) {
            throw new \RuntimeException('Invalid data returned.');
        }
        foreach (array_column($results, 'name') as $name) {
            self::assertMatchesRegularExpression($phpRegexp, $name);
        }

        // article that doesn't match
        /** @var array{name: string} $results */
        $results = $this->connection->fetchAssociative('SELECT name FROM article WHERE id = 1');
        self::assertDoesNotMatchRegularExpression($phpRegexp, $results['name']);
    }
}

---