Merge "Add support for regular expression on supported backend."

6 files changed, 667 insertions, 0 deletions

diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index aca1db33c..cca6a27de 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -960,3 +960,134 @@ class InsertOnDuplicateTest(fixtures.TestBase, AssertsCompiledSQL):
                 "baz_1": "some literal",
             },
         )
+
+
+class RegexpCommon(testing.AssertsCompiledSQL):
+    def setUp(self):
+        self.table = table(
+            "mytable", column("myid", Integer), column("name", String)
+        )
+
+    def test_regexp_match(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match("pattern"),
+            "mytable.myid REGEXP %s",
+            checkpositional=("pattern",),
+        )
+
+    def test_regexp_match_column(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match(self.table.c.name),
+            "mytable.myid REGEXP mytable.name",
+            checkpositional=(),
+        )
+
+    def test_regexp_match_str(self):
+        self.assert_compile(
+            literal("string").regexp_match(self.table.c.name),
+            "%s REGEXP mytable.name",
+            checkpositional=("string",),
+        )
+
+    def test_not_regexp_match(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match("pattern"),
+            "mytable.myid NOT REGEXP %s",
+            checkpositional=("pattern",),
+        )
+
+    def test_not_regexp_match_column(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match(self.table.c.name),
+            "mytable.myid NOT REGEXP mytable.name",
+            checkpositional=(),
+        )
+
+    def test_not_regexp_match_str(self):
+        self.assert_compile(
+            ~literal("string").regexp_match(self.table.c.name),
+            "%s NOT REGEXP mytable.name",
+            checkpositional=("string",),
+        )
+
+    def test_regexp_replace(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace("pattern", "replacement"),
+            "REGEXP_REPLACE(mytable.myid, %s, %s)",
+            checkpositional=("pattern", "replacement"),
+        )
+
+    def test_regexp_replace_column(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace("pattern", self.table.c.name),
+            "REGEXP_REPLACE(mytable.myid, %s, mytable.name)",
+            checkpositional=("pattern",),
+        )
+
+    def test_regexp_replace_column2(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace(self.table.c.name, "replacement"),
+            "REGEXP_REPLACE(mytable.myid, mytable.name, %s)",
+            checkpositional=("replacement",),
+        )
+
+    def test_regexp_replace_string(self):
+        self.assert_compile(
+            literal("string").regexp_replace("pattern", self.table.c.name),
+            "REGEXP_REPLACE(%s, %s, mytable.name)",
+            checkpositional=("string", "pattern"),
+        )
+
+
+class RegexpTestMySql(fixtures.TestBase, RegexpCommon):
+    __dialect__ = "mysql"
+
+    def test_regexp_match_flags(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match("pattern", flags="ig"),
+            "REGEXP_LIKE(mytable.myid, %s, %s)",
+            checkpositional=("pattern", "ig"),
+        )
+
+    def test_not_regexp_match_flags(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match("pattern", flags="ig"),
+            "NOT REGEXP_LIKE(mytable.myid, %s, %s)",
+            checkpositional=("pattern", "ig"),
+        )
+
+    def test_regexp_replace_flags(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace(
+                "pattern", "replacement", flags="ig"
+            ),
+            "REGEXP_REPLACE(mytable.myid, %s, %s, %s)",
+            checkpositional=("pattern", "replacement", "ig"),
+        )
+
+
+class RegexpTestMariaDb(fixtures.TestBase, RegexpCommon):
+    __dialect__ = "mariadb"
+
+    def test_regexp_match_flags(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match("pattern", flags="ig"),
+            "mytable.myid REGEXP CONCAT('(?', %s, ')', %s)",
+            checkpositional=("ig", "pattern"),
+        )
+
+    def test_not_regexp_match_flags(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match("pattern", flags="ig"),
+            "mytable.myid NOT REGEXP CONCAT('(?', %s, ')', %s)",
+            checkpositional=("ig", "pattern"),
+        )
+
+    def test_regexp_replace_flags(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace(
+                "pattern", "replacement", flags="ig"
+            ),
+            "REGEXP_REPLACE(mytable.myid, CONCAT('(?', %s, ')', %s), %s)",
+            checkpositional=("ig", "pattern", "replacement"),
+        )
diff --git a/test/dialect/oracle/test_compiler.py b/test/dialect/oracle/test_compiler.py
index 97a204630..a4a8cd99f 100644
--- a/test/dialect/oracle/test_compiler.py
+++ b/test/dialect/oracle/test_compiler.py
@@ -1307,3 +1307,134 @@ class SequenceTest(fixtures.TestBase, AssertsCompiledSQL):
             dialect.identifier_preparer.format_sequence(seq)
             == '"Some_Schema"."My_Seq"'
         )
+
+
+class RegexpTest(fixtures.TestBase, testing.AssertsCompiledSQL):
+    __dialect__ = "oracle"
+
+    def setUp(self):
+        self.table = table(
+            "mytable", column("myid", Integer), column("name", String)
+        )
+
+    def test_regexp_match(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match("pattern"),
+            "REGEXP_LIKE(mytable.myid, :myid_1)",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_regexp_match_column(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match(self.table.c.name),
+            "REGEXP_LIKE(mytable.myid, mytable.name)",
+            checkparams={},
+        )
+
+    def test_regexp_match_str(self):
+        self.assert_compile(
+            literal("string").regexp_match(self.table.c.name),
+            "REGEXP_LIKE(:param_1, mytable.name)",
+            checkparams={"param_1": "string"},
+        )
+
+    def test_regexp_match_flags(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match("pattern", flags="ig"),
+            "REGEXP_LIKE(mytable.myid, :myid_1, :myid_2)",
+            checkparams={"myid_1": "pattern", "myid_2": "ig"},
+        )
+
+    def test_regexp_match_flags_col(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match("pattern", flags=self.table.c.name),
+            "REGEXP_LIKE(mytable.myid, :myid_1, mytable.name)",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_not_regexp_match(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match("pattern"),
+            "NOT REGEXP_LIKE(mytable.myid, :myid_1)",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_not_regexp_match_column(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match(self.table.c.name),
+            "NOT REGEXP_LIKE(mytable.myid, mytable.name)",
+            checkparams={},
+        )
+
+    def test_not_regexp_match_str(self):
+        self.assert_compile(
+            ~literal("string").regexp_match(self.table.c.name),
+            "NOT REGEXP_LIKE(:param_1, mytable.name)",
+            checkparams={"param_1": "string"},
+        )
+
+    def test_not_regexp_match_flags_col(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match(
+                "pattern", flags=self.table.c.name
+            ),
+            "NOT REGEXP_LIKE(mytable.myid, :myid_1, mytable.name)",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_not_regexp_match_flags(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match("pattern", flags="ig"),
+            "NOT REGEXP_LIKE(mytable.myid, :myid_1, :myid_2)",
+            checkparams={"myid_1": "pattern", "myid_2": "ig"},
+        )
+
+    def test_regexp_replace(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace("pattern", "replacement"),
+            "REGEXP_REPLACE(mytable.myid, :myid_1, :myid_2)",
+            checkparams={"myid_1": "pattern", "myid_2": "replacement"},
+        )
+
+    def test_regexp_replace_column(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace("pattern", self.table.c.name),
+            "REGEXP_REPLACE(mytable.myid, :myid_1, mytable.name)",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_regexp_replace_column2(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace(self.table.c.name, "replacement"),
+            "REGEXP_REPLACE(mytable.myid, mytable.name, :myid_1)",
+            checkparams={"myid_1": "replacement"},
+        )
+
+    def test_regexp_replace_string(self):
+        self.assert_compile(
+            literal("string").regexp_replace("pattern", self.table.c.name),
+            "REGEXP_REPLACE(:param_1, :param_2, mytable.name)",
+            checkparams={"param_2": "pattern", "param_1": "string"},
+        )
+
+    def test_regexp_replace_flags(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace(
+                "pattern", "replacement", flags="ig"
+            ),
+            "REGEXP_REPLACE(mytable.myid, :myid_1, :myid_3, :myid_2)",
+            checkparams={
+                "myid_1": "pattern",
+                "myid_3": "replacement",
+                "myid_2": "ig",
+            },
+        )
+
+    def test_regexp_replace_flags_col(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace(
+                "pattern", "replacement", flags=self.table.c.name
+            ),
+            "REGEXP_REPLACE(mytable.myid, :myid_1, :myid_2, mytable.name)",
+            checkparams={"myid_1": "pattern", "myid_2": "replacement"},
+        )
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 6196b52f2..556601fc6 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -12,6 +12,7 @@ from sqlalchemy import func
 from sqlalchemy import Identity
 from sqlalchemy import Index
 from sqlalchemy import Integer
+from sqlalchemy import literal
 from sqlalchemy import MetaData
 from sqlalchemy import null
 from sqlalchemy import schema
@@ -2346,3 +2347,149 @@ class FullTextSearchTest(fixtures.TestBase, AssertsCompiledSQL):
             "WHERE to_tsvector(%(to_tsvector_1)s, mytable.title) @@ "
             """to_tsquery('english', %(to_tsvector_2)s)""",
         )
+
+
+class RegexpTest(fixtures.TestBase, testing.AssertsCompiledSQL):
+    __dialect__ = "postgresql"
+
+    def setUp(self):
+        self.table = table(
+            "mytable", column("myid", Integer), column("name", String)
+        )
+
+    def test_regexp_match(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match("pattern"),
+            "mytable.myid ~ %(myid_1)s",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_regexp_match_column(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match(self.table.c.name),
+            "mytable.myid ~ mytable.name",
+            checkparams={},
+        )
+
+    def test_regexp_match_str(self):
+        self.assert_compile(
+            literal("string").regexp_match(self.table.c.name),
+            "%(param_1)s ~ mytable.name",
+            checkparams={"param_1": "string"},
+        )
+
+    def test_regexp_match_flags(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match("pattern", flags="ig"),
+            "mytable.myid ~ CONCAT('(?', %(myid_1)s, ')', %(myid_2)s)",
+            checkparams={"myid_2": "pattern", "myid_1": "ig"},
+        )
+
+    def test_regexp_match_flags_ignorecase(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match("pattern", flags="i"),
+            "mytable.myid ~* %(myid_1)s",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_regexp_match_flags_col(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match("pattern", flags=self.table.c.name),
+            "mytable.myid ~ CONCAT('(?', mytable.name, ')', %(myid_1)s)",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_not_regexp_match(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match("pattern"),
+            "mytable.myid !~ %(myid_1)s",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_not_regexp_match_column(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match(self.table.c.name),
+            "mytable.myid !~ mytable.name",
+            checkparams={},
+        )
+
+    def test_not_regexp_match_str(self):
+        self.assert_compile(
+            ~literal("string").regexp_match(self.table.c.name),
+            "%(param_1)s !~ mytable.name",
+            checkparams={"param_1": "string"},
+        )
+
+    def test_not_regexp_match_flags(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match("pattern", flags="ig"),
+            "mytable.myid !~ CONCAT('(?', %(myid_1)s, ')', %(myid_2)s)",
+            checkparams={"myid_2": "pattern", "myid_1": "ig"},
+        )
+
+    def test_not_regexp_match_flags_ignorecase(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match("pattern", flags="i"),
+            "mytable.myid !~* %(myid_1)s",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_not_regexp_match_flags_col(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match(
+                "pattern", flags=self.table.c.name
+            ),
+            "mytable.myid !~ CONCAT('(?', mytable.name, ')', %(myid_1)s)",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_regexp_replace(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace("pattern", "replacement"),
+            "REGEXP_REPLACE(mytable.myid, %(myid_1)s, %(myid_2)s)",
+            checkparams={"myid_1": "pattern", "myid_2": "replacement"},
+        )
+
+    def test_regexp_replace_column(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace("pattern", self.table.c.name),
+            "REGEXP_REPLACE(mytable.myid, %(myid_1)s, mytable.name)",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_regexp_replace_column2(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace(self.table.c.name, "replacement"),
+            "REGEXP_REPLACE(mytable.myid, mytable.name, %(myid_1)s)",
+            checkparams={"myid_1": "replacement"},
+        )
+
+    def test_regexp_replace_string(self):
+        self.assert_compile(
+            literal("string").regexp_replace("pattern", self.table.c.name),
+            "REGEXP_REPLACE(%(param_1)s, %(param_2)s, mytable.name)",
+            checkparams={"param_2": "pattern", "param_1": "string"},
+        )
+
+    def test_regexp_replace_flags(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace(
+                "pattern", "replacement", flags="ig"
+            ),
+            "REGEXP_REPLACE(mytable.myid, %(myid_1)s, %(myid_3)s, %(myid_2)s)",
+            checkparams={
+                "myid_1": "pattern",
+                "myid_3": "replacement",
+                "myid_2": "ig",
+            },
+        )
+
+    def test_regexp_replace_flags_col(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace(
+                "pattern", "replacement", flags=self.table.c.name
+            ),
+            "REGEXP_REPLACE(mytable.myid, %(myid_1)s,"
+            " %(myid_2)s, mytable.name)",
+            checkparams={"myid_1": "pattern", "myid_2": "replacement"},
+        )
diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py
index b4813926f..cb418e99d 100644
--- a/test/dialect/test_sqlite.py
+++ b/test/dialect/test_sqlite.py
@@ -20,6 +20,7 @@ from sqlalchemy import ForeignKey
 from sqlalchemy import func
 from sqlalchemy import Index
 from sqlalchemy import inspect
+from sqlalchemy import literal
 from sqlalchemy import MetaData
 from sqlalchemy import pool
 from sqlalchemy import PrimaryKeyConstraint
@@ -27,6 +28,7 @@ from sqlalchemy import schema
 from sqlalchemy import select
 from sqlalchemy import sql
 from sqlalchemy import Table
+from sqlalchemy import table
 from sqlalchemy import testing
 from sqlalchemy import text
 from sqlalchemy import tuple_
@@ -2608,3 +2610,76 @@ class TypeReflectionTest(fixtures.TestBase):
 
     def test_round_trip_direct_type_affinity(self):
         self._test_round_trip(self._type_affinity_fixture())
+
+
+class RegexpTest(fixtures.TestBase, testing.AssertsCompiledSQL):
+    __dialect__ = "sqlite"
+
+    def setUp(self):
+        self.table = table(
+            "mytable", column("myid", Integer), column("name", String)
+        )
+
+    def test_regexp_match(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match("pattern"),
+            "mytable.myid REGEXP ?",
+            checkpositional=("pattern",),
+        )
+
+    def test_regexp_match_column(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match(self.table.c.name),
+            "mytable.myid REGEXP mytable.name",
+            checkparams={},
+        )
+
+    def test_regexp_match_str(self):
+        self.assert_compile(
+            literal("string").regexp_match(self.table.c.name),
+            "? REGEXP mytable.name",
+            checkpositional=("string",),
+        )
+
+    def test_regexp_match_flags(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match("pattern", flags="ig"),
+            "mytable.myid REGEXP ?",
+            checkpositional=("pattern",),
+        )
+
+    def test_not_regexp_match(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match("pattern"),
+            "mytable.myid NOT REGEXP ?",
+            checkpositional=("pattern",),
+        )
+
+    def test_not_regexp_match_flags(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match("pattern", flags="ig"),
+            "mytable.myid NOT REGEXP ?",
+            checkpositional=("pattern",),
+        )
+
+    def test_not_regexp_match_column(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match(self.table.c.name),
+            "mytable.myid NOT REGEXP mytable.name",
+            checkparams={},
+        )
+
+    def test_not_regexp_match_str(self):
+        self.assert_compile(
+            ~literal("string").regexp_match(self.table.c.name),
+            "? NOT REGEXP mytable.name",
+            checkpositional=("string",),
+        )
+
+    def test_regexp_replace(self):
+        assert_raises_message(
+            exc.CompileError,
+            "sqlite dialect does not support regular expression replacements",
+            self.table.c.myid.regexp_replace("pattern", "rep").compile,
+            dialect=sqlite.dialect(),
+        )
diff --git a/test/requirements.py b/test/requirements.py
index c2fd65b05..6d4b3dc90 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -1651,6 +1651,14 @@ class DefaultRequirements(SuiteRequirements):
         return self.computed_columns + skip_if("oracle")
 
     @property
+    def regexp_match(self):
+        return only_on(["postgresql", "mysql", "mariadb", "oracle", "sqlite"])
+
+    @property
+    def regexp_replace(self):
+        return only_on(["postgresql", "mysql>=8", "mariadb", "oracle"])
+
+    @property
     def supports_distinct_on(self):
         """If a backend supports the DISTINCT ON in a select"""
         return only_if(["postgresql"])
diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py
index fcf40ebbd..2e5ce8daa 100644
--- a/test/sql/test_operators.py
+++ b/test/sql/test_operators.py
@@ -2365,6 +2365,181 @@ class MatchTest(fixtures.TestBase, testing.AssertsCompiledSQL):
         )
 
 
+class RegexpTest(fixtures.TestBase, testing.AssertsCompiledSQL):
+    __dialect__ = "default"
+
+    def setUp(self):
+        self.table = table(
+            "mytable", column("myid", Integer), column("name", String)
+        )
+
+    def test_regexp_match(self):
+        assert_raises_message(
+            exc.CompileError,
+            "default dialect does not support regular expressions",
+            self.table.c.myid.regexp_match("pattern").compile,
+            dialect=default.DefaultDialect(),
+        )
+
+    def test_not_regexp_match(self):
+        assert_raises_message(
+            exc.CompileError,
+            "default dialect does not support regular expressions",
+            (~self.table.c.myid.regexp_match("pattern")).compile,
+            dialect=default.DefaultDialect(),
+        )
+
+    def test_regexp_replace(self):
+        assert_raises_message(
+            exc.CompileError,
+            "default dialect does not support regular expression replacements",
+            self.table.c.myid.regexp_replace("pattern", "rep").compile,
+            dialect=default.DefaultDialect(),
+        )
+
+
+class RegexpTestStrCompiler(fixtures.TestBase, testing.AssertsCompiledSQL):
+    __dialect__ = "default_enhanced"
+
+    def setUp(self):
+        self.table = table(
+            "mytable", column("myid", Integer), column("name", String)
+        )
+
+    def test_regexp_match(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match("pattern"),
+            "mytable.myid <regexp> :myid_1",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_regexp_match_column(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match(self.table.c.name),
+            "mytable.myid <regexp> mytable.name",
+            checkparams={},
+        )
+
+    def test_regexp_match_str(self):
+        self.assert_compile(
+            literal("string").regexp_match(self.table.c.name),
+            ":param_1 <regexp> mytable.name",
+            checkparams={"param_1": "string"},
+        )
+
+    def test_regexp_match_flags(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_match("pattern", flags="ig"),
+            "mytable.myid <regexp> :myid_1",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_not_regexp_match(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match("pattern"),
+            "mytable.myid <not regexp> :myid_1",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_not_regexp_match_column(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match(self.table.c.name),
+            "mytable.myid <not regexp> mytable.name",
+            checkparams={},
+        )
+
+    def test_not_regexp_match_str(self):
+        self.assert_compile(
+            ~literal("string").regexp_match(self.table.c.name),
+            ":param_1 <not regexp> mytable.name",
+            checkparams={"param_1": "string"},
+        )
+
+    def test_not_regexp_match_flags(self):
+        self.assert_compile(
+            ~self.table.c.myid.regexp_match("pattern", flags="ig"),
+            "mytable.myid <not regexp> :myid_1",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_regexp_replace(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace("pattern", "replacement"),
+            "<regexp replace>(mytable.myid, :myid_1, :myid_2)",
+            checkparams={"myid_1": "pattern", "myid_2": "replacement"},
+        )
+
+    def test_regexp_replace_column(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace("pattern", self.table.c.name),
+            "<regexp replace>(mytable.myid, :myid_1, mytable.name)",
+            checkparams={"myid_1": "pattern"},
+        )
+
+    def test_regexp_replace_column2(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace(self.table.c.name, "replacement"),
+            "<regexp replace>(mytable.myid, mytable.name, :myid_1)",
+            checkparams={"myid_1": "replacement"},
+        )
+
+    def test_regexp_replace_string(self):
+        self.assert_compile(
+            literal("string").regexp_replace("pattern", self.table.c.name),
+            "<regexp replace>(:param_1, :param_2, mytable.name)",
+            checkparams={"param_2": "pattern", "param_1": "string"},
+        )
+
+    def test_regexp_replace_flags(self):
+        self.assert_compile(
+            self.table.c.myid.regexp_replace(
+                "pattern", "replacement", flags="ig"
+            ),
+            "<regexp replace>(mytable.myid, :myid_1, :myid_2)",
+            checkparams={"myid_1": "pattern", "myid_2": "replacement"},
+        )
+
+    def test_regexp_precedence_1(self):
+        self.assert_compile(
+            and_(
+                self.table.c.myid.match("foo"),
+                self.table.c.myid.regexp_match("xx"),
+            ),
+            "mytable.myid MATCH :myid_1 AND " "mytable.myid <regexp> :myid_2",
+        )
+        self.assert_compile(
+            and_(
+                self.table.c.myid.match("foo"),
+                ~self.table.c.myid.regexp_match("xx"),
+            ),
+            "mytable.myid MATCH :myid_1 AND "
+            "mytable.myid <not regexp> :myid_2",
+        )
+        self.assert_compile(
+            and_(
+                self.table.c.myid.match("foo"),
+                self.table.c.myid.regexp_replace("xx", "yy"),
+            ),
+            "mytable.myid MATCH :myid_1 AND "
+            "<regexp replace>(mytable.myid, :myid_2, :myid_3)",
+        )
+
+    def test_regexp_precedence_2(self):
+        self.assert_compile(
+            self.table.c.myid + self.table.c.myid.regexp_match("xx"),
+            "mytable.myid + (mytable.myid <regexp> :myid_1)",
+        )
+        self.assert_compile(
+            self.table.c.myid + ~self.table.c.myid.regexp_match("xx"),
+            "mytable.myid + (mytable.myid <not regexp> :myid_1)",
+        )
+        self.assert_compile(
+            self.table.c.myid + self.table.c.myid.regexp_replace("xx", "yy"),
+            "mytable.myid + ("
+            "<regexp replace>(mytable.myid, :myid_1, :myid_2))",
+        )
+
+
 class ComposedLikeOperatorsTest(fixtures.TestBase, testing.AssertsCompiledSQL):
     __dialect__ = "default"