Is there a database that can store a regular expression as values?

Question

I am looking for a database that can store regular expression expressions as values. For example. something like that:

{: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 can return strings / documents based on whether the query matches the expression or not (for example, “FIND WHERE strings" tt "= ~: expression"). And as a result, we get the lines Tim and Fil. Most databases can do the exact opposite (check if the text field matches the regular expression query). But, unfortunately, neither Mongo nor Postgres can do the opposite.

PS Or maybe I'm wrong, and there are some extensions for postgres or mongo that allow me to store a regex?

Answer 1

The Oracle database can do this.

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

You want to select a regular expression from a column, see the example SQL Fiddle example below.

---

SQL Fiddle

Select an Oracle database.

In the schematic window, do 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; 

Run an SQL statement, for example. (as you mentioned in your question):

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

Productivity:

 NAME COUNT REGEXP Tim 3 t+ Fil 1 tt 

---

Link here .

Excerpts:

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

And a few more questions to consider:

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

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

Please note: there is no column named RegEx, you need to save the row as is in the text column.

You can also use RegEx in constraint checking and column design.

Answer 2

MongoDB will allow you to store the actual regular expressions (i.e. not a string representing the 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 query strings with a regular expression that matches the query, as follows:

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

Here we look for lines where the string 'a' maps to the myregex field, which returns a second document with the returned regular expression /a+/ .