Is there a database that can store regex as values?

Question

I am looking for a database that can store regex expressions as values. E.g. somthing like this:

{:name => "Tim", :count => 3, :expression => /t+/},
{:name => "Rob", :count => 4, :expression => /a\d+/},
{:name => "Fil", :count => 1, :expression => /tt/},
{:name => "Marc", :count => 1, :expression => /bb/}

So I could return rows/documents based on whether the query matches the expression or not (e.g."FIND rows WHERE "tt" =~ :expression"). And get Tim and Fil rows as the result. Most databases can do the exactly opposite thing (check whether a text field matches a regex query). But neither mongo nor postgres can do the opposite thing, unfortunately.

P.S. Or perhaps I am wrong and there are some extensions for postgres or mongo that allow me to store regex?

Answer 1

MongoDB will allow you to store actual regular expressions (i.e. not a string representing a regular expression), as shown below:

> db.mycoll.insertOne({myregex: /aa/})
{
    "acknowledged" : true,
    "insertedId" : ObjectId("5826414249bf0898c1059b38")
}
> db.mycoll.insertOne({myregex: /a+/})
{
    "acknowledged" : true,
    "insertedId" : ObjectId("5826414949bf0898c1059b39")
}
> db.mycoll.find()
{ "_id" : ObjectId("5826414249bf0898c1059b38"), "myregex" : /aa/ }
{ "_id" : ObjectId("5826414949bf0898c1059b39"), "myregex" : /a+/ }

You can use this to then query for rows with a regex that matches a query, as follows:

> db.mycoll.find(function() { return this.myregex.test('a'); } )
{ "_id" : ObjectId("5826414949bf0898c1059b39"), "myregex" : /a+/ }

Here we search for rows where the string 'a' is matched by the myregex field, resulting in the second document, with regex /a+/, being returned.

Answer 2

Oracle database can do that.

Example query: WHERE REGEXP_LIKE(first_name, '^Ste(v|ph)en$')

You want to select an regexp from a column, See SQL Fiddle example below for an example.

---

SQL Fiddle

Choose Oracle database.

In schema window execute the following:

CREATE TABLE regexp (name VARCHAR2(20), count NUMBER, regexp VARCHAR2(50));

INSERT INTO regexp VALUES ('Tim', 3, 't+');
INSERT INTO regexp VALUES ('Rob', 4, 'a\d+');
INSERT INTO regexp VALUES ('Fil', 1, 'tt');
INSERT INTO regexp VALUES ('Marc', 1, 'bb');
COMMIT;

Execute an SQL statement, e.g. (as you mentioned in your question):

SELECT * FROM regexp WHERE REGEXP_LIKE('tt', regexp);

Yields:

NAME    COUNT   REGEXP
Tim     3       t+
Fil     1       tt

---

Reference here.

Excerpt:

Oracle Database implements regular expression support with a set of Oracle Database SQL functions and conditions that enable you to search and manipulate string data. You can use these functions in any environment that supports Oracle Database SQL. You can use these functions on a text literal, bind variable, or any column that holds character data such as CHAR, NCHAR, CLOB, NCLOB, NVARCHAR2, and VARCHAR2 (but not LONG).

And some more info to consider:

A string literal in a REGEXP function or condition conforms to the rules of SQL text literals. By default, regular expressions must be enclosed in single quotes. If your regular expression includes the single quote character, then enter two single quotation marks to represent one single quotation mark within the expression. This technique ensures that the entire expression is interpreted by the SQL function and improves the readability of your code. You can also use the q-quote syntax to define your own character to terminate a text literal. For example, you could delimit your regular expression with the pound sign (#) and then use a single quote within the expression.

Note: If your expression comes from a column or a bind variable, then the same rules for quoting do not apply.

Note there is no column type named RegEx, you would need to save the string as is, in a textual column.

Also you can use RegEx in constraint checking and when you project columns.