Regex

Available since: v3.0.0

A regex can be created by casting from a string.

The following examples all return true.

-- Either 'a' or 'b'
<regex> "a|b" = "a";

-- Either color or colour
<regex> "col(o|ou)r" = "colour";

-- Case-insensitive match on English color, colour, or French couleur
<regex> "((?i)col(o|ou)r|couleur)" = "COULEUR";

While regex was added as a standalone type in version 2.3.0, regex matching has always been available via the string::matches() function.

string::matches("a", "a|b");
string::matches("colour", "col(o|ou)r");
string::matches("COULEUR", "((?i)col(o|ou)r|couleur)");

A regex can also be written as a literal between forward slashes, which is the shortest form inside a WHERE clause.

CREATE article:1 SET text = "News from Bitfinicon";
CREATE article:2 SET text = "Other news";
SELECT VALUE id FROM article WHERE text = /(?i)bitfinicon/;

[article:1]

A regex match in a WHERE clause does not use an index, including a FULLTEXT index on the same field, so the query reads every record in the table. For word-based search over a large table, a full-text search index queried with the @@ operator uses the index.