Repository: iterative/datachain

Issue:
Need regexp_replace() function 
### Description

It is common to transform text columns in datasets.

For example, if  class is encoded as part of the filename, one currently needs to write a mapper to extract label "cat" from the name "cat.1001.jpg" :

```
import re

def extract_label(filepath: str) -> str:
    match = re.search(r'\.(\pL+)\.', filepath)
    
    if match:
        return match.group(1)
    else:
        return None

map(label = lambda file:  extract_label(file.path))
```

It should be straightforward in many SQL engines to use regexp_replace() for this part:

```
mutate(label = regexp_replace(Column("file.path"), '\.(\pL+)\.', '\1') ))

```

Discussion:
EdwardLi-coder: > Thanks @EdwardLi-coder ! It would be great to add a test for this.

Thank you @shcheklein  for pointing that out. I apologize for forgetting to add a test. I will add one now.
shcheklein: Looks good, but we need also implement the ClickHouse part (that is used in the private Studio product). I'm getting atm:

```python
NotImplementedError: Compiler not implemented for the SQLAlchemy function, string.regexp_replace, with dialect, clickhouse. For information on adding dialect-specific compilers, see https://docs.sqlalchemy.org/en/14/core/compiler.html
```

let me try to help you here and create a companion PR is possible.
shcheklein: I've added a companion PR to the Studio (private) repo. Made small changes here (@EdwardLi-coder please review). It should be good to go @iterative/datachain 

Patch:
diff --git a/src/datachain/sql/functions/string.py b/src/datachain/sql/functions/string.py
index 25b7b2c..7d8c3c9 100644
--- a/src/datachain/sql/functions/string.py
+++ b/src/datachain/sql/functions/string.py
@@ -26,5 +26,17 @@ class split(GenericFunction):  # noqa: N801
     inherit_cache = True
 
 
+class regexp_replace(GenericFunction):  # noqa: N801
+    """
+    Replaces substring that match a regular expression.
+    """
+
+    type = String()
+    package = "string"
+    name = "regexp_replace"
+    inherit_cache = True
+
+
 compiler_not_implemented(length)
 compiler_not_implemented(split)
+compiler_not_implemented(regexp_replace)
diff --git a/src/datachain/sql/sqlite/base.py b/src/datachain/sql/sqlite/base.py
index 43ef7a3..fe8d6ec 100644
--- a/src/datachain/sql/sqlite/base.py
+++ b/src/datachain/sql/sqlite/base.py
@@ -1,4 +1,5 @@
 import logging
+import re
 import sqlite3
 from collections.abc import Iterable
 from datetime import MAXYEAR, MINYEAR, datetime, timezone
@@ -77,6 +78,7 @@ def setup():
     compiles(array.length, "sqlite")(compile_array_length)
     compiles(string.length, "sqlite")(compile_string_length)
     compiles(string.split, "sqlite")(compile_string_split)
+    compiles(string.regexp_replace, "sqlite")(compile_regexp_replace)
     compiles(conditional.greatest, "sqlite")(compile_greatest)
     compiles(conditional.least, "sqlite")(compile_least)
     compiles(Values, "sqlite")(compile_values)
@@ -178,9 +180,15 @@ def register_user_defined_sql_functions() -> None:
 
     _registered_function_creators["vector_functions"] = create_vector_functions
 
+    def sqlite_regexp_replace(string: str, pattern: str, replacement: str) -> str:
+        return re.sub(pattern, replacement, string)
+
     def create_string_functions(conn):
         conn.create_function("split", 2, sqlite_string_split, deterministic=True)
         conn.create_function("split", 3, sqlite_string_split, deterministic=True)
+        conn.create_function(
+            "regexp_replace", 3, sqlite_regexp_replace, deterministic=True
+        )
 
     _registered_function_creators["string_functions"] = create_string_functions
 
@@ -265,6 +273,10 @@ def path_file_ext(path):
     return func.substr(path, func.length(path) - path_file_ext_length(path) + 1)
 
 
+def compile_regexp_replace(element, compiler, **kwargs):
+    return f"regexp_replace({compiler.process(element.clauses, **kwargs)})"
+
+
 def compile_path_parent(element, compiler, **kwargs):
     return compiler.process(path_parent(*element.clauses.clauses), **kwargs)
 


Test patch:
diff --git a/tests/unit/sql/test_string.py b/tests/unit/sql/test_string.py
index 0842614..e4a0674 100644
--- a/tests/unit/sql/test_string.py
+++ b/tests/unit/sql/test_string.py
@@ -21,3 +21,28 @@ def test_split(warehouse, args, expected):
     query = select(string.split(*args))
     result = tuple(warehouse.dataset_rows_select(query))
     assert result == ((expected,),)
+
+
+@pytest.mark.parametrize(
+    "input_string,pattern,replacement,expected",
+    [
+        ("hello world", "world", "universe", "hello universe"),
+        ("abc123def456", r"\d+", "X", "abcXdefX"),
+        ("cat.1001.jpg", r"\.(\w+)\.", r"_\1_", "cat_1001_jpg"),
+        (
+            "dog_photo.jpg",
+            r"(\w+)\.(jpg|jpeg|png|gif)$",
+            r"\1_thumb.\2",
+            "dog_photo_thumb.jpg",
+        ),
+        ("file.with...dots.txt", r"\.+", ".", "file.with.dots.txt"),
+    ],
+)
+def test_regexp_replace(warehouse, input_string, pattern, replacement, expected):
+    query = select(
+        string.regexp_replace(
+            literal(input_string), literal(pattern), literal(replacement)
+        )
+    )
+    result = tuple(warehouse.db.execute(query))
+    assert result == ((expected,),)