Repository: tobymao/sqlglot
Issue category: Bug Report

BigQuery function `CONTAINS_SUBSTRING` should be `CONTAINS_SUBSTR`
I believe there is an issue in the BigQuery dialect. sqlglot identifies and correctly transpiles a function called `CONTAINS_SUBSTRING`, but it appears the function is actually called `CONTAINS_SUBSTR` (see [docs](https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#contains_substr)). The function `CONTAINS_SUBSTRING` results in an error as the function is not recognized by BigQuery.

https://github.com/tobymao/sqlglot/blob/73825d2d11cefaabd2ed73c3dcb9184393b6c042/sqlglot/dialects/bigquery.py#L320

It would be great if someone could independently verify and potentially fix this.


Functions to edit:
sqlglot/dialects/bigquery.py:_build_contains_substring
sqlglot/dialects/bigquery.py:BigQuery
sqlglot/dialects/bigquery.py:Generator.contains_sql
Functions added: sqlglot/dialects/bigquery.py:BigQuery

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 2f3ac53efb..ec8188bb7c 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -319,7 +319,7 @@ def _builder(args: t.List) -> exp.TimeToStr:
 
 def _build_contains_substring(args: t.List) -> exp.Contains | exp.Anonymous:
     if len(args) == 3:
-        return exp.Anonymous(this="CONTAINS_SUBSTRING", expressions=args)
+        return exp.Anonymous(this="CONTAINS_SUBSTR", expressions=args)
 
     # Lowercase the operands in case of transpilation, as exp.Contains
     # is case-sensitive on other dialects
@@ -492,7 +492,7 @@ class Parser(parser.Parser):
 
         FUNCTIONS = {
             **parser.Parser.FUNCTIONS,
-            "CONTAINS_SUBSTRING": _build_contains_substring,
+            "CONTAINS_SUBSTR": _build_contains_substring,
             "DATE": _build_date,
             "DATE_ADD": build_date_delta_with_interval(exp.DateAdd),
             "DATE_SUB": build_date_delta_with_interval(exp.DateSub),
@@ -1219,4 +1219,4 @@ def contains_sql(self, expression: exp.Contains) -> str:
                 this = this.this
                 expr = expr.this
 
-            return self.func("CONTAINS_SUBSTRING", this, expr)
+            return self.func("CONTAINS_SUBSTR", this, expr)

Test patch: diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 26b12a15fa..09f7673cef 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -1608,11 +1608,11 @@ def test_bigquery(self):
         )
 
         self.validate_identity(
-            "CONTAINS_SUBSTRING(a, b, json_scope => 'JSON_KEYS_AND_VALUES')"
+            "CONTAINS_SUBSTR(a, b, json_scope => 'JSON_KEYS_AND_VALUES')"
         ).assert_is(exp.Anonymous)
 
         self.validate_all(
-            """CONTAINS_SUBSTRING(a, b)""",
+            """CONTAINS_SUBSTR(a, b)""",
             read={
                 "": "CONTAINS(a, b)",
                 "spark": "CONTAINS(a, b)",
@@ -1628,7 +1628,7 @@ def test_bigquery(self):
                 "snowflake": "CONTAINS(LOWER(a), LOWER(b))",
                 "duckdb": "CONTAINS(LOWER(a), LOWER(b))",
                 "oracle": "CONTAINS(LOWER(a), LOWER(b))",
-                "bigquery": "CONTAINS_SUBSTRING(a, b)",
+                "bigquery": "CONTAINS_SUBSTR(a, b)",
             },
         )