Repository: tobymao/sqlglot
Pull request: Feat(doris):add support for to_number(x)
fix [#3093](https://github.com/tobymao/sqlglot/issues/3093)

Problem statement:
TO_NUMBER(number)  is parsed wrongly from oracle to doris
**Before you file an issue**
- Make sure you specify the "read" dialect eg. `parse_one(sql, read="spark")`
- Make sure you specify the "write" dialect eg. `ast.sql(dialect="duckdb")`
- Check if the issue still exists on main

**Fully reproducible code snippet**
Please include a fully reproducible code snippet or the input sql, dialect, and expected output.
```
import sqlglot
sql="""
SELECT to_number(x) from tmp;
"""
sqlglot.transpile(sql,read="oracle", write="doris")[0]
```
output is below:
`SELECT to_number(x) from tmp;`
oracle has to_number function,but doris don't have it.I think it should be converted to cast(x as double)

**Official Documentation**
Please include links to official SQL documentation related to your issue.

Hints from issue discussion:
This is a bit more nuanced than just simply converting `TO_NUMBER` into a cast at parse time, because there are more arguments involved and you need to preserve them. It's not high priority for us so it's out of scope right now, but feel free to make a PR if you need it. I'd suggest looking at how `TO_CHAR` is handled first.

Commit message: fix make style error

Patch to review:
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index bccdad0f4f..dc358e5e9a 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -97,6 +97,7 @@ class Parser(parser.Parser):
             "TO_CHAR": _build_timetostr_or_tochar,
             "TO_TIMESTAMP": build_formatted_time(exp.StrToTime, "oracle"),
             "TO_DATE": build_formatted_time(exp.StrToDate, "oracle"),
+            "TO_NUMBER": lambda args: exp.ToNumber.from_arg_list(args),
         }
 
         FUNCTION_PARSERS: t.Dict[str, t.Callable] = {
@@ -246,6 +247,7 @@ class Generator(generator.Generator):
             exp.TemporaryProperty: lambda _, e: f"{e.name or 'GLOBAL'} TEMPORARY",
             exp.TimeToStr: lambda self, e: self.func("TO_CHAR", e.this, self.format_time(e)),
             exp.ToChar: lambda self, e: self.function_fallback_sql(e),
+            exp.ToNumber: lambda self, e: self.function_fallback_sql(e),
             exp.Trim: trim_sql,
             exp.UnixToTime: lambda self,
             e: f"TO_DATE('1970-01-01', 'YYYY-MM-DD') + ({self.sql(e, 'this')} / 86400)",
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 8b88c9e5ff..71bed1325b 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -4462,6 +4462,10 @@ class ToChar(Func):
     arg_types = {"this": True, "format": False, "nlsparam": False}
 
 
+class ToNumber(Func):
+    arg_types = {"this": True, "format": False, "nlsparam": False}
+
+
 # https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#syntax
 class Convert(Func):
     arg_types = {"this": True, "expression": True, "style": False}
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index f8375f9afc..060869f1e7 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -3247,6 +3247,11 @@ def tochar_sql(self, expression: exp.ToChar) -> str:
 
         return self.sql(exp.cast(expression.this, "text"))
 
+    def tonumber_sql(self, expression: exp.ToNumber) -> str:
+        if expression.args.get("format"):
+            self.unsupported("Format argument unsupported for TO_NUMBER function")
+        return self.sql(exp.cast(expression.this, "double"))
+
     def dictproperty_sql(self, expression: exp.DictProperty) -> str:
         this = self.sql(expression, "this")
         kind = self.sql(expression, "kind")
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 67e1846031..fdb71e7e7e 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -102,6 +102,13 @@ def test_oracle(self):
                 "oracle": "TO_CHAR(x)",
             },
         )
+        self.validate_all(
+            "TO_NUMBER(x)",
+            write={
+                "doris": "CAST(x AS DOUBLE)",
+                "oracle": "TO_NUMBER(x)",
+            },
+        )
         self.validate_all(
             "SELECT TO_CHAR(TIMESTAMP '1999-12-01 10:00:00')",
             write={


Code review comments:
diff_hunk: @@ -97,6 +97,7 @@ class Parser(parser.Parser):
             "TO_CHAR": _build_timetostr_or_tochar,
             "TO_TIMESTAMP": build_formatted_time(exp.StrToTime, "oracle"),
             "TO_DATE": build_formatted_time(exp.StrToDate, "oracle"),
+            "TO_NUMBER": lambda args: exp.ToNumber.from_arg_list(args),
path: sqlglot/dialects/oracle.py
text: @user1:
This is redundant.
```suggestion
```