Repository: tobymao/sqlglot

Issue:
Support for INTERVAL Syntax of Spark/Databricks
This currently fails:

```python
import sqlglot

sql = """select cast('23:00:00' as interval hour to second)"""
sqlglot.parse_one(sql, read="databricks")
```

**Official Documentation**
https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html


Discussion:

Patch:
diff --git a/sqlglot/dialects/spark.py b/sqlglot/dialects/spark.py
index a4435f66..9d4a1abe 100644
--- a/sqlglot/dialects/spark.py
+++ b/sqlglot/dialects/spark.py
@@ -35,6 +35,13 @@ def _parse_datediff(args: t.List) -> exp.Expression:
 
 
 class Spark(Spark2):
+    class Tokenizer(Spark2.Tokenizer):
+        RAW_STRINGS = [
+            (prefix + q, q)
+            for q in t.cast(t.List[str], Spark2.Tokenizer.QUOTES)
+            for prefix in ("r", "R")
+        ]
+
     class Parser(Spark2.Parser):
         FUNCTIONS = {
             **Spark2.Parser.FUNCTIONS,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 0479da09..e2ee106d 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -3992,16 +3992,11 @@ class TimeUnit(Expression):
         super().__init__(**args)
 
 
-# https://www.oracletutorial.com/oracle-basics/oracle-interval/
-# https://trino.io/docs/current/language/types.html#interval-year-to-month
-class IntervalYearToMonthSpan(Expression):
-    arg_types = {}
-
-
 # https://www.oracletutorial.com/oracle-basics/oracle-interval/
 # https://trino.io/docs/current/language/types.html#interval-day-to-second
-class IntervalDayToSecondSpan(Expression):
-    arg_types = {}
+# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
+class IntervalSpan(Expression):
+    arg_types = {"this": True, "expression": True}
 
 
 class Interval(TimeUnit):
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 306df812..fd2d132c 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -72,8 +72,7 @@ class Generator:
         exp.ExternalProperty: lambda self, e: "EXTERNAL",
         exp.HeapProperty: lambda self, e: "HEAP",
         exp.InlineLengthColumnConstraint: lambda self, e: f"INLINE LENGTH {self.sql(e, 'this')}",
-        exp.IntervalDayToSecondSpan: "DAY TO SECOND",
-        exp.IntervalYearToMonthSpan: "YEAR TO MONTH",
+        exp.IntervalSpan: lambda self, e: f"{self.sql(e, 'this')} TO {self.sql(e, 'expression')}",
         exp.LanguageProperty: lambda self, e: self.naked_property(e),
         exp.LocationProperty: lambda self, e: self.naked_property(e),
         exp.LogProperty: lambda self, e: f"{'NO ' if e.args.get('no') else ''}LOG",
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index f8690d5d..4e558743 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3325,15 +3325,14 @@ class Parser(metaclass=_Parser):
             elif self._match_text_seq("WITHOUT", "TIME", "ZONE"):
                 maybe_func = False
         elif type_token == TokenType.INTERVAL:
-            if self._match_text_seq("YEAR", "TO", "MONTH"):
-                span: t.Optional[t.List[exp.Expression]] = [exp.IntervalYearToMonthSpan()]
-            elif self._match_text_seq("DAY", "TO", "SECOND"):
-                span = [exp.IntervalDayToSecondSpan()]
+            unit = self._parse_var()
+
+            if self._match_text_seq("TO"):
+                span = [exp.IntervalSpan(this=unit, expression=self._parse_var())]
             else:
                 span = None
 
-            unit = not span and self._parse_var()
-            if not unit:
+            if span or not unit:
                 this = self.expression(
                     exp.DataType, this=exp.DataType.Type.INTERVAL, expressions=span
                 )


Test patch:
diff --git a/tests/dialects/test_databricks.py b/tests/dialects/test_databricks.py
index f13d0f2b..d06e0f17 100644
--- a/tests/dialects/test_databricks.py
+++ b/tests/dialects/test_databricks.py
@@ -5,6 +5,12 @@ class TestDatabricks(Validator):
     dialect = "databricks"
 
     def test_databricks(self):
+        self.validate_identity("SELECT CAST('11 23:4:0' AS INTERVAL DAY TO HOUR)")
+        self.validate_identity("SELECT CAST('11 23:4:0' AS INTERVAL DAY TO MINUTE)")
+        self.validate_identity("SELECT CAST('11 23:4:0' AS INTERVAL DAY TO SECOND)")
+        self.validate_identity("SELECT CAST('23:00:00' AS INTERVAL HOUR TO MINUTE)")
+        self.validate_identity("SELECT CAST('23:00:00' AS INTERVAL HOUR TO SECOND)")
+        self.validate_identity("SELECT CAST('23:00:00' AS INTERVAL MINUTE TO SECOND)")
         self.validate_identity("CREATE TABLE target SHALLOW CLONE source")
         self.validate_identity("INSERT INTO a REPLACE WHERE cond VALUES (1), (2)")
         self.validate_identity("SELECT c1 : price")
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index a892b0f1..becb66a1 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -239,6 +239,14 @@ TBLPROPERTIES (
         self.validate_identity("TRIM(LEADING 'SL' FROM 'SSparkSQLS')")
         self.validate_identity("TRIM(TRAILING 'SL' FROM 'SSparkSQLS')")
         self.validate_identity("SPLIT(str, pattern, lim)")
+        self.validate_identity(
+            "SELECT REGEXP_REPLACE('100-200', r'([^0-9])', '')",
+            "SELECT REGEXP_REPLACE('100-200', '([^0-9])', '')",
+        )
+        self.validate_identity(
+            "SELECT REGEXP_REPLACE('100-200', R'([^0-9])', '')",
+            "SELECT REGEXP_REPLACE('100-200', '([^0-9])', '')",
+        )
         self.validate_identity(
             "SELECT STR_TO_MAP('a:1,b:2,c:3')",
             "SELECT STR_TO_MAP('a:1,b:2,c:3', ',', ':')",