11 files changed, 443 insertions, 3 deletions
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 46529636d..0c9859e79 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1759,6 +1759,62 @@ class MySQLCompiler(compiler.SQLCompiler):
             self.process(binary.right),
         )
 
+    def _mariadb_regexp_flags(self, flags, pattern, **kw):
+        return "CONCAT('(?', %s, ')', %s)" % (
+            self.process(flags, **kw),
+            self.process(pattern, **kw),
+        )
+
+    def _regexp_match(self, op_string, binary, operator, **kw):
+        flags = binary.modifiers["flags"]
+        if flags is None:
+            return self._generate_generic_binary(binary, op_string, **kw)
+        elif self.dialect.is_mariadb:
+            return "%s%s%s" % (
+                self.process(binary.left, **kw),
+                op_string,
+                self._mariadb_regexp_flags(flags, binary.right),
+            )
+        else:
+            text = "REGEXP_LIKE(%s, %s, %s)" % (
+                self.process(binary.left, **kw),
+                self.process(binary.right, **kw),
+                self.process(flags, **kw),
+            )
+            if op_string == " NOT REGEXP ":
+                return "NOT %s" % text
+            else:
+                return text
+
+    def visit_regexp_match_op_binary(self, binary, operator, **kw):
+        return self._regexp_match(" REGEXP ", binary, operator, **kw)
+
+    def visit_not_regexp_match_op_binary(self, binary, operator, **kw):
+        return self._regexp_match(" NOT REGEXP ", binary, operator, **kw)
+
+    def visit_regexp_replace_op_binary(self, binary, operator, **kw):
+        flags = binary.modifiers["flags"]
+        replacement = binary.modifiers["replacement"]
+        if flags is None:
+            return "REGEXP_REPLACE(%s, %s, %s)" % (
+                self.process(binary.left, **kw),
+                self.process(binary.right, **kw),
+                self.process(replacement, **kw),
+            )
+        elif self.dialect.is_mariadb:
+            return "REGEXP_REPLACE(%s, %s, %s)" % (
+                self.process(binary.left, **kw),
+                self._mariadb_regexp_flags(flags, binary.right),
+                self.process(replacement, **kw),
+            )
+        else:
+            return "REGEXP_REPLACE(%s, %s, %s, %s)" % (
+                self.process(binary.left, **kw),
+                self.process(binary.right, **kw),
+                self.process(replacement, **kw),
+                self.process(flags, **kw),
+            )
+
 
 class MySQLDDLCompiler(compiler.DDLCompiler):
     def get_column_specification(self, column, **kw):
diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 2e5ce2581..65580b4e1 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -23,7 +23,7 @@ available, which are the use of IDENTITY columns (Oracle 12 and above only)
 or the association of a SEQUENCE with the column.
 
 Specifying GENERATED AS IDENTITY (Oracle 12 and above)
-^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 Starting from version 12 Oracle can make use of identity columns using
 the :class:`_sql.Identity` to specify the autoincrementing behavior::
@@ -53,7 +53,7 @@ setting :paramref:`_schema.Identity.on_null` to ``True`` to specify ON NULL
 in conjunction with a 'BY DEFAULT' identity column.
 
 Using a SEQUENCE (all Oracle versions)
-^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 Older version of Oracle had no "autoincrement"
 feature, SQLAlchemy relies upon sequences to produce these values.   With the
@@ -1211,6 +1211,43 @@ class OracleCompiler(compiler.SQLCompiler):
             self.process(binary.right),
         )
 
+    def _get_regexp_args(self, binary, kw):
+        string = self.process(binary.left, **kw)
+        pattern = self.process(binary.right, **kw)
+        flags = binary.modifiers["flags"]
+        if flags is not None:
+            flags = self.process(flags, **kw)
+        return string, pattern, flags
+
+    def visit_regexp_match_op_binary(self, binary, operator, **kw):
+        string, pattern, flags = self._get_regexp_args(binary, kw)
+        if flags is None:
+            return "REGEXP_LIKE(%s, %s)" % (string, pattern)
+        else:
+            return "REGEXP_LIKE(%s, %s, %s)" % (string, pattern, flags)
+
+    def visit_not_regexp_match_op_binary(self, binary, operator, **kw):
+        return "NOT %s" % self.visit_regexp_match_op_binary(
+            binary, operator, **kw
+        )
+
+    def visit_regexp_replace_op_binary(self, binary, operator, **kw):
+        string, pattern, flags = self._get_regexp_args(binary, kw)
+        replacement = self.process(binary.modifiers["replacement"], **kw)
+        if flags is None:
+            return "REGEXP_REPLACE(%s, %s, %s)" % (
+                string,
+                pattern,
+                replacement,
+            )
+        else:
+            return "REGEXP_REPLACE(%s, %s, %s, %s)" % (
+                string,
+                pattern,
+                replacement,
+                flags,
+            )
+
 
 class OracleDDLCompiler(compiler.DDLCompiler):
     def define_constraint_cascades(self, constraint):
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 07405e6d1..8eb116111 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1796,6 +1796,53 @@ class PGCompiler(compiler.SQLCompiler):
             else ""
         )
 
+    def _regexp_match(self, base_op, binary, operator, kw):
+        flags = binary.modifiers["flags"]
+        if flags is None:
+            return self._generate_generic_binary(
+                binary, " %s " % base_op, **kw
+            )
+        if isinstance(flags, elements.BindParameter) and flags.value == "i":
+            return self._generate_generic_binary(
+                binary, " %s* " % base_op, **kw
+            )
+        flags = self.process(flags, **kw)
+        string = self.process(binary.left, **kw)
+        pattern = self.process(binary.right, **kw)
+        return "%s %s CONCAT('(?', %s, ')', %s)" % (
+            string,
+            base_op,
+            flags,
+            pattern,
+        )
+
+    def visit_regexp_match_op_binary(self, binary, operator, **kw):
+        return self._regexp_match("~", binary, operator, kw)
+
+    def visit_not_regexp_match_op_binary(self, binary, operator, **kw):
+        return self._regexp_match("!~", binary, operator, kw)
+
+    def visit_regexp_replace_op_binary(self, binary, operator, **kw):
+        string = self.process(binary.left, **kw)
+        pattern = self.process(binary.right, **kw)
+        flags = binary.modifiers["flags"]
+        if flags is not None:
+            flags = self.process(flags, **kw)
+        replacement = self.process(binary.modifiers["replacement"], **kw)
+        if flags is None:
+            return "REGEXP_REPLACE(%s, %s, %s)" % (
+                string,
+                pattern,
+                replacement,
+            )
+        else:
+            return "REGEXP_REPLACE(%s, %s, %s, %s)" % (
+                string,
+                pattern,
+                replacement,
+                flags,
+            )
+
     def visit_empty_set_expr(self, element_types):
         # cast the empty set to the type we are comparing against.  if
         # we are comparing against the null type, pick an arbitrary
diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py
index 3a8ffa23d..8ef35514a 100644
--- a/lib/sqlalchemy/dialects/sqlite/base.py
+++ b/lib/sqlalchemy/dialects/sqlite/base.py
@@ -1077,6 +1077,12 @@ class SQLiteCompiler(compiler.SQLCompiler):
             ", ".join("1" for type_ in element_types or [INTEGER()]),
         )
 
+    def visit_regexp_match_op_binary(self, binary, operator, **kw):
+        return self._generate_generic_binary(binary, " REGEXP ", **kw)
+
+    def visit_not_regexp_match_op_binary(self, binary, operator, **kw):
+        return self._generate_generic_binary(binary, " NOT REGEXP ", **kw)
+
 
 class SQLiteDDLCompiler(compiler.DDLCompiler):
     def get_column_specification(self, column, **kwargs):
diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
index 3c88dab8e..a8b234921 100644
--- a/lib/sqlalchemy/dialects/sqlite/pysqlite.py
+++ b/lib/sqlalchemy/dialects/sqlite/pysqlite.py
@@ -120,6 +120,46 @@ that creates a Python sqlite3 driver level connection directly.
     `Uniform Resource Identifiers <https://www.sqlite.org/uri.html>`_ - in
     the SQLite documentation
 
+.. _pysqlite_regexp:
+
+Regular Expression Support
+---------------------------
+
+.. versionadded:: 1.4
+
+Support for the :meth:`_sql.ColumnOperators.regexp_match` operator is provided
+using Python's re.match_ function.  SQLite itself does not include a working
+regular expression operator; instead, it includes a non-implemented placeholder
+operator ``REGEXP`` that calls a user-defined function that must be provided.
+
+SQLAlchemy's implementation makes use of the pysqlite create_function_ hook
+as follows::
+
+
+    def regexp(a, b):
+        return bool(re.match(a, b))
+
+    sqlite_connection.create_function(
+        "regexp", 2, regexp,
+    )
+
+There is currently no support for regular expression flags as a separate
+argument, as these are not supported by SQLite's REGEXP operator, however these
+may be included inline within the regular expression string.  See `Python regular expressions`_ for
+details.
+
+.. seealso::
+
+    `Python regular expressions`_: Documentation for Python's regular expression syntax.
+
+.. _create_function: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
+
+.. _re.match: https://docs.python.org/3/library/re.html#re.match
+
+.. _Python regular expressions: https://docs.python.org/3/library/re.html#re.match
+
+
+
 Compatibility with sqlite3 "native" date and datetime types
 -----------------------------------------------------------
 
@@ -362,6 +402,7 @@ by adding the desired locking mode to our ``"BEGIN"``::
 """  # noqa
 
 import os
+import re
 
 from .base import DATE
 from .base import DATETIME
@@ -461,6 +502,37 @@ class SQLiteDialect_pysqlite(SQLiteDialect):
                 connection, level
             )
 
+    def on_connect(self):
+        connect = super(SQLiteDialect_pysqlite, self).on_connect()
+
+        def regexp(a, b):
+            return bool(re.match(a, b))
+
+        def set_regexp(connection):
+            if hasattr(connection, "connection"):
+                dbapi_connection = connection.connection
+            else:
+                dbapi_connection = connection
+
+            dbapi_connection.create_function(
+                "regexp", 2, regexp,
+            )
+
+        fns = [set_regexp]
+
+        if self.isolation_level is not None:
+
+            def iso_level(conn):
+                self.set_isolation_level(conn, self.isolation_level)
+
+            fns.append(iso_level)
+
+        def connect(conn):
+            for fn in fns:
+                fn(conn)
+
+        return connect
+
     def create_connect_args(self, url):
         if url.username or url.password or url.host or url.port:
             raise exc.ArgumentError(
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 7b917e661..ec1a57935 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -2109,6 +2109,24 @@ class SQLCompiler(Compiled):
             **kw
         )
 
+    def visit_regexp_match_op_binary(self, binary, operator, **kw):
+        raise exc.CompileError(
+            "%s dialect does not support regular expressions"
+            % self.dialect.name
+        )
+
+    def visit_not_regexp_match_op_binary(self, binary, operator, **kw):
+        raise exc.CompileError(
+            "%s dialect does not support regular expressions"
+            % self.dialect.name
+        )
+
+    def visit_regexp_replace_op_binary(self, binary, operator, **kw):
+        raise exc.CompileError(
+            "%s dialect does not support regular expression replacements"
+            % self.dialect.name
+        )
+
     def visit_bindparam(
         self,
         bindparam,
@@ -3671,6 +3689,20 @@ class StrSQLCompiler(SQLCompiler):
     def get_from_hint_text(self, table, text):
         return "[%s]" % text
 
+    def visit_regexp_match_op_binary(self, binary, operator, **kw):
+        return self._generate_generic_binary(binary, " <regexp> ", **kw)
+
+    def visit_not_regexp_match_op_binary(self, binary, operator, **kw):
+        return self._generate_generic_binary(binary, " <not regexp> ", **kw)
+
+    def visit_regexp_replace_op_binary(self, binary, operator, **kw):
+        replacement = binary.modifiers["replacement"]
+        return "<regexp replace>(%s, %s, %s)" % (
+            binary.left._compiler_dispatch(self, **kw),
+            binary.right._compiler_dispatch(self, **kw),
+            replacement._compiler_dispatch(self, **kw),
+        )
+
 
 class DDLCompiler(Compiled):
     @util.memoized_property
diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py
index 6f1a25670..eec174e8b 100644
--- a/lib/sqlalchemy/sql/default_comparator.py
+++ b/lib/sqlalchemy/sql/default_comparator.py
@@ -252,6 +252,45 @@ def _collate_impl(expr, op, other, **kw):
     return collate(expr, other)
 
 
+def _regexp_match_impl(expr, op, pattern, flags, **kw):
+    if flags is not None:
+        flags = coercions.expect(
+            roles.BinaryElementRole,
+            flags,
+            expr=expr,
+            operator=operators.regexp_replace_op,
+        )
+    return _boolean_compare(
+        expr,
+        op,
+        pattern,
+        flags=flags,
+        negate=operators.not_regexp_match_op
+        if op is operators.regexp_match_op
+        else operators.regexp_match_op,
+        **kw
+    )
+
+
+def _regexp_replace_impl(expr, op, pattern, replacement, flags, **kw):
+    replacement = coercions.expect(
+        roles.BinaryElementRole,
+        replacement,
+        expr=expr,
+        operator=operators.regexp_replace_op,
+    )
+    if flags is not None:
+        flags = coercions.expect(
+            roles.BinaryElementRole,
+            flags,
+            expr=expr,
+            operator=operators.regexp_replace_op,
+        )
+    return _binary_operate(
+        expr, op, pattern, replacement=replacement, flags=flags, **kw
+    )
+
+
 # a mapping of operators with the method they use, along with
 # their negated operator for comparison operators
 operator_lookup = {
@@ -304,4 +343,7 @@ operator_lookup = {
     "lshift": (_unsupported_impl,),
     "rshift": (_unsupported_impl,),
     "contains": (_unsupported_impl,),
+    "regexp_match_op": (_regexp_match_impl,),
+    "not_regexp_match_op": (_regexp_match_impl,),
+    "regexp_replace_op": (_regexp_replace_impl,),
 }
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index d60c63363..31584f072 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -44,6 +44,7 @@ __all__ = [
     "case",
     "cast",
     "column",
+    "custom_op",
     "cte",
     "delete",
     "desc",
@@ -81,6 +82,7 @@ __all__ = [
     "union",
     "union_all",
     "update",
+    "quoted_name",
     "within_group",
     "Subquery",
     "TableSample",
@@ -141,6 +143,9 @@ from .functions import modifier  # noqa
 from .lambdas import lambda_stmt  # noqa
 from .lambdas import LambdaElement  # noqa
 from .lambdas import StatementLambdaElement  # noqa
+from .operators import ColumnOperators  # noqa
+from .operators import custom_op  # noqa
+from .operators import Operators  # noqa
 from .selectable import Alias  # noqa
 from .selectable import AliasedReturnsRows  # noqa
 from .selectable import CompoundSelect  # noqa
diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py
index bb773e281..91a0792c3 100644
--- a/lib/sqlalchemy/sql/operators.py
+++ b/lib/sqlalchemy/sql/operators.py
@@ -913,7 +913,7 @@ class ColumnOperators(Operators):
     def match(self, other, **kwargs):
         """Implements a database-specific 'match' operator.
 
-        :meth:`~.ColumnOperators.match` attempts to resolve to
+        :meth:`_sql.ColumnOperators.match` attempts to resolve to
         a MATCH-like function or operator provided by the backend.
         Examples include:
 
@@ -928,6 +928,96 @@ class ColumnOperators(Operators):
         """
         return self.operate(match_op, other, **kwargs)
 
+    def regexp_match(self, pattern, flags=None):
+        """Implements a database-specific 'regexp match' operator.
+
+        E.g.::
+
+            stmt = select(table.c.some_column).where(
+                table.c.some_column.regexp_match('^(b|c)')
+            )
+
+        :meth:`_sql.ColumnOperators.regexp_match` attempts to resolve to
+        a REGEXP-like function or operator provided by the backend, however
+        the specific regular expression syntax and flags available are
+        **not backend agnostic**.
+
+        Examples include:
+
+        * PostgreSQL - renders ``x ~ y`` or ``x !~ y`` when negated.
+        * Oracle - renders ``REGEXP_LIKE(x, y)``
+        * SQLite - uses SQLite's ``REGEXP`` placeholder operator and calls into
+          the Python ``re.match()`` builtin.
+        * other backends may provide special implementations.
+        * Backends without any special implementation will emit
+          the operator as "REGEXP" or "NOT REGEXP".  This is compatible with
+          SQLite and MySQL, for example.
+
+        Regular expression support is currently implemented for Oracle,
+        PostgreSQL, MySQL and MariaDB.  Partial support is available for
+        SQLite.  Support among third-party dialects may vary.
+
+        :param pattern: The regular expression pattern string or column
+          clause.
+        :param flags: Any regular expression string flags to apply. Flags
+          tend to be backend specific. It can be a string or a column clause.
+          Some backends, like PostgreSQL and MariaDB, may alternatively
+          specify the flags as part of the pattern.
+          When using the ignore case flag 'i' in PostgreSQL, the ignore case
+          regexp match operator ``~*`` or ``!~*`` will be used.
+
+        .. versionadded:: 1.4
+
+        .. seealso::
+
+            :meth:`_sql.ColumnOperators.regexp_replace`
+
+
+        """
+        return self.operate(regexp_match_op, pattern, flags=flags)
+
+    def regexp_replace(self, pattern, replacement, flags=None):
+        """Implements a database-specific 'regexp replace' operator.
+
+        E.g.::
+
+            stmt = select(
+                table.c.some_column.regexp_replace(
+                    'b(..)',
+                    'X\1Y',
+                    flags='g'
+                )
+            )
+
+        :meth:`_sql.ColumnOperators.regexp_replace` attempts to resolve to
+        a REGEXP_REPLACE-like function provided by the backend, that
+        usually emit the function ``REGEXP_REPLACE()``.  However,
+        the specific regular expression syntax and flags available are
+        **not backend agnostic**.
+
+        Regular expression replacement support is currently implemented for
+        Oracle, PostgreSQL, MySQL 8 or greater and MariaDB.  Support among
+        third-party dialects may vary.
+
+        :param pattern: The regular expression pattern string or column
+          clause.
+        :param pattern: The replacement string or column clause.
+        :param flags: Any regular expression string flags to apply. Flags
+          tend to be backend specific. It can be a string or a column clause.
+          Some backends, like PostgreSQL and MariaDB, may alternatively
+          specify the flags as part of the pattern.
+
+        .. versionadded:: 1.4
+
+        .. seealso::
+
+            :meth:`_sql.ColumnOperators.regexp_match`
+
+        """
+        return self.operate(
+            regexp_replace_op, pattern, replacement=replacement, flags=flags
+        )
+
     def desc(self):
         """Produce a :func:`_expression.desc` clause against the
         parent object."""
@@ -1299,6 +1389,20 @@ def match_op(a, b, **kw):
 
 
 @comparison_op
+def regexp_match_op(a, b, flags=None):
+    return a.regexp_match(b, flags=flags)
+
+
+@comparison_op
+def not_regexp_match_op(a, b, flags=None):
+    return ~a.regexp_match(b, flags=flags)
+
+
+def regexp_replace_op(a, b, replacement, flags=None):
+    return a.regexp_replace(b, replacement=replacement, flags=flags)
+
+
+@comparison_op
 def notmatch_op(a, b, **kw):
     return a.notmatch(b, **kw)
 
@@ -1417,6 +1521,9 @@ _PRECEDENCE = {
     filter_op: 6,
     match_op: 5,
     notmatch_op: 5,
+    regexp_match_op: 5,
+    not_regexp_match_op: 5,
+    regexp_replace_op: 5,
     ilike_op: 5,
     notilike_op: 5,
     like_op: 5,
diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 7b0ddafe3..9b8caac2e 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -1280,3 +1280,22 @@ class SuiteRequirements(Requirements):
         This is mainly to exclude MSSql.
         """
         return exclusions.closed()
+
+    @property
+    def regexp_match(self):
+        """backend supports the regexp_match operator.
+
+        .. versionadded:: 1.4
+
+        """
+        return exclusions.closed()
+
+    @property
+    def regexp_replace(self):
+        """backend supports the regexp_replace operator.
+
+        .. versionadded:: 1.4
+
+
+        """
+        return exclusions.closed()
diff --git a/lib/sqlalchemy/testing/suite/test_select.py b/lib/sqlalchemy/testing/suite/test_select.py
index 675fac609..7e0337146 100644
--- a/lib/sqlalchemy/testing/suite/test_select.py
+++ b/lib/sqlalchemy/testing/suite/test_select.py
@@ -1004,6 +1004,23 @@ class LikeFunctionsTest(fixtures.TablesTest):
         self._test(col.contains("b%cd", autoescape=True, escape="#"), {3})
         self._test(col.contains("b#cd", autoescape=True, escape="#"), {7})
 
+    @testing.requires.regexp_match
+    def test_regexp_match(self):
+        col = self.tables.some_table.c.data
+        self._test(col.regexp_match("a.cde"), {1, 5, 6, 9})
+
+    @testing.requires.regexp_match
+    def test_not_regexp_match(self):
+        col = self.tables.some_table.c.data
+        self._test(~col.regexp_match("a.cde"), {2, 3, 4, 7, 8, 10})
+
+    @testing.requires.regexp_replace
+    def test_regexp_replace(self):
+        col = self.tables.some_table.c.data
+        self._test(
+            col.regexp_replace("a.cde", "FOO").contains("FOO"), {1, 5, 6, 9}
+        )
+
 
 class ComputedColumnTest(fixtures.TablesTest):
     __backend__ = True