Repository: dodopizza/sqlalchemy-kusto

Issue:
Unable to generate KQL for (i)like with percent wildcards
**Describe the bug**
When executing (i)like the KQL generated is not correct.


**To Reproduce**
Run the following code as an example:
Column("Field2", Integer).ilike("abc%")

**Expected behavior**
The position of the percent wildcard should be used to determine if the KQL should use startwith, endswith, has, as well as use the _cs variant when using the case-sensitive LIKE.

**Screenshots**
n/a

**Desktop (please complete the following information):**
 - OS: [e.g. iOS]
 - Browser [e.g. chrome, safari]
 - Version [e.g. 22]

**Smartphone (please complete the following information):**
 - Device: [e.g. iPhone6]
 - OS: [e.g. iOS8.1]
 - Browser [e.g. stock browser, safari]
 - Version [e.g. 22]

**Additional context**
Will follow up with PR.

Discussion:

Solution patch:
diff --git a/sqlalchemy_kusto/dialect_kql.py b/sqlalchemy_kusto/dialect_kql.py
index b1f0122..ef09a86 100644
--- a/sqlalchemy_kusto/dialect_kql.py
+++ b/sqlalchemy_kusto/dialect_kql.py
@@ -299,21 +299,101 @@ class KustoKqlCompiler(compiler.SQLCompiler):
         where_clause = re.sub(r"\s*>\s*=\s*", ">=", where_clause, flags=re.IGNORECASE)
         where_clause = where_clause.replace(">==", ">=")
         where_clause = where_clause.replace("<==", "<=")
+        where_clause = where_clause.replace("<>", "!=")
+        where_clause = re.sub(
+            r"(\s|^)lower\(", r"\1tolower(", where_clause, flags=re.IGNORECASE
+        )
+
         where_clause = re.sub(
             r"(\s)(<>|!=)\s*", r" \2 ", where_clause, flags=re.IGNORECASE
         )  # Handle '!=' and '<>'
         where_clause = re.sub(
             r"(\s)(<|<=|>|>=)\s*", r" \2 ", where_clause, flags=re.IGNORECASE
         )  # Comparison operators: <, <=, >, >=
-        # Step 3: Handle 'LIKE' -> 'has' for substring matching
-        where_clause = re.sub(
-            r"(\s)LIKE\s*", r"\1has ", where_clause, flags=re.IGNORECASE
-        )  # Replace LIKE with has
+
+        # Step 3: Handle '(I)LIKE' -> 'has' for substring matching
+        # support the % characters in the RHS at the beginning, end or both
+        like_regexp = r"(\s){like}\s+([a-z_]+\()?(['\"]){pre}([^%]+){post}\3(\))?"
+        if re.search(r"NOT I?LIKE", where_clause, re.IGNORECASE):
+            where_clause = re.sub(
+                like_regexp.format(like="NOT LIKE", pre="", post="%+"),
+                r"\1!startswith_cs \2\3\4\3\5",
+                where_clause,
+                flags=re.IGNORECASE,
+            )
+            where_clause = re.sub(
+                like_regexp.format(like="NOT LIKE", pre="%+", post=""),
+                r"\1!endswith_cs \2\3\4\3\5",
+                where_clause,
+                flags=re.IGNORECASE,
+            )
+            where_clause = re.sub(
+                like_regexp.format(like="NOT LIKE", pre="%+", post="%+"),
+                r"\1!has_cs \2\3\4\3\5",
+                where_clause,
+                flags=re.IGNORECASE,
+            )
+            where_clause = re.sub(
+                like_regexp.format(like="NOT ILIKE", pre="", post="%+"),
+                r"\1!startswith \2\3\4\3\5",
+                where_clause,
+                flags=re.IGNORECASE,
+            )
+            where_clause = re.sub(
+                like_regexp.format(like="NOT ILIKE", pre="%+", post=""),
+                r"\1!endswith \2\3\4\3\5",
+                where_clause,
+                flags=re.IGNORECASE,
+            )
+            where_clause = re.sub(
+                like_regexp.format(like="NOT ILIKE", pre="%+", post="%+"),
+                r"\1!has \2\3\4\3\5",
+                where_clause,
+                flags=re.IGNORECASE,
+            )
+        elif re.search(r"I?LIKE", where_clause, re.IGNORECASE):
+            where_clause = re.sub(
+                like_regexp.format(like="LIKE", pre="%+", post="%+"),
+                r"\1has_cs \2\3\4\3\5",
+                where_clause,
+                flags=re.IGNORECASE,
+            )
+            where_clause = re.sub(
+                like_regexp.format(like="LIKE", pre="", post="%+"),
+                r"\1startswith_cs \2\3\4\3\5",
+                where_clause,
+                flags=re.IGNORECASE,
+            )
+            where_clause = re.sub(
+                like_regexp.format(like="LIKE", pre="%+", post=""),
+                r"\1endswith_cs \2\3\4\3\5",
+                where_clause,
+                flags=re.IGNORECASE,
+            )
+            where_clause = re.sub(
+                like_regexp.format(like="ILIKE", pre="%+", post="%+"),
+                r"\1has \2\3\4\3\5",
+                where_clause,
+                flags=re.IGNORECASE,
+            )
+            where_clause = re.sub(
+                like_regexp.format(like="ILIKE", pre="", post="%+"),
+                r"\1startswith \2\3\4\3\5",
+                where_clause,
+                flags=re.IGNORECASE,
+            )
+            where_clause = re.sub(
+                like_regexp.format(like="ILIKE", pre="%+", post=""),
+                r"\1endswith \2\3\4\3\5",
+                where_clause,
+                flags=re.IGNORECASE,
+            )
+
         # Step 4: Handle 'IN' and 'NOT IN' operators (with lists inside parentheses)
         # We need to correctly handle multiple spaces around IN/NOT IN and lists inside parentheses
         where_clause = re.sub(
             r"(\s)NOT IN\s*\(([^)]+)\)",
-            r"\1not in (\2)",
+            r"\1!in (\2)",
             where_clause,
             flags=re.IGNORECASE,
         )  # NOT IN operator (list of values)


Test patch:
diff --git a/tests/unit/test_dialect_kql.py b/tests/unit/test_dialect_kql.py
index 3138951..7ced313 100644
--- a/tests/unit/test_dialect_kql.py
+++ b/tests/unit/test_dialect_kql.py
@@ -85,9 +85,45 @@ def test_select_from_text():
         ),
         pytest.param(
             Column("Field1", String).notin_(["1", "One"]),
-            """(["Field1"] not in ('1', 'One'))""",
+            """(["Field1"] !in ('1', 'One'))""",
         ),
         pytest.param(text("Field1 = '1'"), """Field1 == '1'"""),
+        pytest.param(text("Field1 <> '1'"), """Field1 != '1'"""),
+        pytest.param(text("Field1 LIKE '%123%'"), """Field1 has_cs '123'"""),
+        pytest.param(text('Field1 LIKE "%123%"'), 'Field1 has_cs "123"'),
+        pytest.param(text("Field1 NOT LIKE '%123%'"), """Field1 !has_cs '123'"""),
+        pytest.param(text('Field1 NOT LIKE "%123%"'), 'Field1 !has_cs "123"'),
+        pytest.param(text("Field1 LIKE '123%'"), """Field1 startswith_cs '123'"""),
+        pytest.param(text('Field1 LIKE "123%"'), 'Field1 startswith_cs "123"'),
+        pytest.param(text("Field1 NOT LIKE '123%'"), """Field1 !startswith_cs '123'"""),
+        pytest.param(text('Field1 NOT LIKE "123%"'), 'Field1 !startswith_cs "123"'),
+        pytest.param(text("Field1 LIKE '%123'"), """Field1 endswith_cs '123'"""),
+        pytest.param(text('Field1 LIKE "%123"'), 'Field1 endswith_cs "123"'),
+        pytest.param(text("Field1 NOT LIKE '%123'"), """Field1 !endswith_cs '123'"""),
+        pytest.param(text('Field1 NOT LIKE "%123"'), 'Field1 !endswith_cs "123"'),
+        pytest.param(text("Field1 ILIKE '%123%'"), """Field1 has '123'"""),
+        pytest.param(text('Field1 ILIKE "%123%"'), 'Field1 has "123"'),
+        pytest.param(text("Field1 NOT ILIKE '%123%'"), """Field1 !has '123'"""),
+        pytest.param(text('Field1 NOT ILIKE "%123%"'), 'Field1 !has "123"'),
+        pytest.param(text("Field1 ILIKE '123%'"), """Field1 startswith '123'"""),
+        pytest.param(text('Field1 ILIKE "123%"'), 'Field1 startswith "123"'),
+        pytest.param(text("Field1 NOT ILIKE '123%'"), """Field1 !startswith '123'"""),
+        pytest.param(text('Field1 NOT ILIKE "123%"'), 'Field1 !startswith "123"'),
+        pytest.param(text("Field1 ILIKE '%123'"), """Field1 endswith '123'"""),
+        pytest.param(text('Field1 ILIKE "%123"'), 'Field1 endswith "123"'),
+        pytest.param(text("Field1 NOT ILIKE '%123'"), """Field1 !endswith '123'"""),
+        pytest.param(text('Field1 NOT ILIKE "%123"'), 'Field1 !endswith "123"'),
+        pytest.param(
+            Column("Field2", Integer).ilike("abc%"),
+            """tolower(["Field2"]) startswith_cs tolower('abc')""",
+        ),
+        pytest.param(
+            Column("Field2", Integer).like("%abc"), """["Field2"] endswith_cs 'abc'"""
+        ),
+        pytest.param(
+            Column("Field2", Integer).notlike("%abc"),
+            """["Field2"] !endswith_cs 'abc'""",
+        ),
         pytest.param(
             Column("Field2", Integer).between(2, 4), """["Field2"] between (2..4)"""
         ),
@@ -99,13 +135,13 @@ def test_select_from_text():
             (Column("Field2", Integer).isnot(None)).__and__(
                 Column("Field1", String).notin_(["1", "One"])
             ),
-            """isnotnull(["Field2"]) and (["Field1"] not in ('1', 'One'))""",
+            """isnotnull(["Field2"]) and (["Field1"] !in ('1', 'One'))""",
         ),
         pytest.param(
             (Column("Field2", Integer).isnot(None)).__or__(
                 Column("Field1", String).notin_(["1", "One"])
             ),
-            """isnotnull(["Field2"]) or (["Field1"] not in ('1', 'One'))""",
+            """isnotnull(["Field2"]) or (["Field1"] !in ('1', 'One'))""",
         ),
     ],
 )