Use MySQL REGEXP in Laravel

1 minute read

Quick note to use MySQL REGEXP in Laravel

You can use these syntaxes in Laravel when you are searching data in a database column with JSON data type (MySQL > 5.7.8) or text data type stored JSON data(MySQL < 5.7).

$query = USER::all();
//laravel < 5.6
$query->where('COLUMN', 'REGEXP', '...');
//laravel > 5.6
$query->whereJsonContains('COLUMN', 'VALUE');

MySQL supports another type of pattern matching operation based on regular expressions. If you are familiar with regular expressions, here are some tips you could step in easier.

Same as normal:

Pattern What the pattern matches
^ Beginning of string
$ End of String
. any single character
* 0 or more instances of preceding element
+ 1 pr more
? 0 or 1
[…] Any character listed between the square brackets
[^…] Any character NOT listed between the square brackets
{n} n instances of preceding element
{m,n} m through n instances of preceding element
(…) Capturing Groups

Some different:

using POSIX class instead of Shorthand Character Classes. For example:

shorthand POSIX What the pattern matches ASCII
\w [:word:] Word characters (letters, numbers and underscores) [A-Za-z0-9_]
\d [:digit:] Digits [0-9]
\s [:space:] All whitespace characters, including line breaks [ \t\r\n\v\f]

more detail

for example:

if we have a permissions column with json type below:


and we want to select all values in the JSON data type are true:

$query->where('permissions', 'REGEXP', '[{]("backend\.[[:alpha:]\.\_]+":true[,]?)+[}]');

{"":true,"backend.promotion":true,"":ture} //pass
{"":false,"backend.promotion":true,"":ture} //fail