Repository: tobymao/sqlglot

Issue:
[Postgres 14.4] Parser does not recognize 'both' keyword in trim() function
Example (valid sql):
```
select trim(both from ' xxx ');
```
Result:
```
ParseError: Expecting ). Line 1, Col: 18.
  select trim(both from ' xxx ');

Expected table name. Line 1, Col: 25.
  select trim(both from ' xxx ');

Required keyword: 'this' missing for <class 'sqlglot.expressions.Table'>. Line 1, Col: 25.
  select trim(both from ' xxx ');

... and 1 more
```

Discussion:
GeorgeSittas: > what do you mean hive isn’t supported?

I mean that we can still parse hive but transpilation _to_ hive for trim is not fully correct. The standard `TRIM(target)` will be transpiled correctly, but `TRIM(TRAILING FROM target)` transpiles to itself, which is invalid hive. We expect `RTRIM(target)`.

> we need to make sure hive works well because it’s a very important target

Yeah, makes sense. I'll try to fix the issue in another commit.
mpf82: LTRIM and RTRIM are widely supported, whereas the ``TRIM( [{LEADING | TRAILING | BOTH}] ...)`` notation seems to be additional contructs supported by a minority of dialects (mysql and postgres, it seems?)

So may I suggest to add full support for TRIM, RTRIM and LTRIM and transpile TRIM with  [{LEADING | TRAILING | BOTH}] to RTRIM/LTRIM respectively? 

e.g. ``TRIM(TRAILING FROM target)`` will transpile to ``RTRIM(target)`` by default in all dialects, even postgres and mysql.
GeorgeSittas: I did as Mike suggested, with one small difference: when reading postgres / mysql, it generates the dialect-specific trim sql only if it contains the "remove charset" or "collation" parts (which are valid in said dialects).

Patch:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 93800a62..a2d39a02 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -49,6 +49,21 @@ def _str_to_date_sql(self, expression):
     return f"STR_TO_DATE({self.sql(expression.this)}, {date_format})"
 
 
+def _trim_sql(self, expression):
+    target = self.sql(expression, "this")
+    trim_type = self.sql(expression, "position")
+    remove_chars = self.sql(expression, "expression")
+
+    # Use TRIM/LTRIM/RTRIM syntax if the expression isn't mysql-specific
+    if not remove_chars:
+        return self.trim_sql(expression)
+
+    trim_type = f"{trim_type} " if trim_type else ""
+    remove_chars = f"{remove_chars} " if remove_chars else ""
+    from_part = "FROM " if trim_type or remove_chars else ""
+    return f"TRIM({trim_type}{remove_chars}{from_part}{target})"
+
+
 def _date_add(expression_class):
     def func(args):
         interval = list_get(args, 1)
@@ -160,4 +175,5 @@ class MySQL(Dialect):
             exp.DateTrunc: _date_trunc_sql,
             exp.StrToDate: _str_to_date_sql,
             exp.StrToTime: _str_to_date_sql,
+            exp.Trim: _trim_sql,
         }
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 61a5122e..7c2fd0ea 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -43,6 +43,23 @@ def _substring_sql(self, expression):
     return f"SUBSTRING({this}{from_part}{for_part})"
 
 
+def _trim_sql(self, expression):
+    target = self.sql(expression, "this")
+    trim_type = self.sql(expression, "position")
+    remove_chars = self.sql(expression, "expression")
+    collation = self.sql(expression, "collation")
+
+    # Use TRIM/LTRIM/RTRIM syntax if the expression isn't postgres-specific
+    if not remove_chars and not collation:
+        return self.trim_sql(expression)
+
+    trim_type = f"{trim_type} " if trim_type else ""
+    remove_chars = f"{remove_chars} " if remove_chars else ""
+    from_part = "FROM " if trim_type or remove_chars else ""
+    collation = f" COLLATE {collation}" if collation else ""
+    return f"TRIM({trim_type}{remove_chars}{from_part}{target}{collation})"
+
+
 class Postgres(Dialect):
     null_ordering = "nulls_are_large"
     time_format = "'YYYY-MM-DD HH24:MI:SS'"
@@ -118,5 +135,6 @@ class Postgres(Dialect):
             exp.Substring: _substring_sql,
             exp.TimeToStr: lambda self, e: f"TO_CHAR({self.sql(e, 'this')}, {self.format_time(e)})",
             exp.TableSample: no_tablesample_sql,
+            exp.Trim: _trim_sql,
             exp.TryCast: no_trycast_sql,
         }
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 12d5d721..d9be73a3 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2382,6 +2382,15 @@ class TimeStrToUnix(Func):
     pass
 
 
+class Trim(Func):
+    arg_types = {
+        "this": True,
+        "position": False,
+        "expression": False,
+        "collation": False,
+    }
+
+
 class TsOrDsAdd(Func, TimeUnit):
     arg_types = {"this": True, "expression": True, "unit": False}
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 793cff0b..b569e123 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -879,6 +879,17 @@ class Generator:
         expression_sql = self.sql(expression, "expression")
         return f"EXTRACT({this} FROM {expression_sql})"
 
+    def trim_sql(self, expression):
+        target = self.sql(expression, "this")
+        trim_type = self.sql(expression, "position")
+
+        if trim_type == "LEADING":
+            return f"LTRIM({target})"
+        elif trim_type == "TRAILING":
+            return f"RTRIM({target})"
+        else:
+            return f"TRIM({target})"
+
     def check_sql(self, expression):
         this = self.sql(expression, key="this")
         return f"CHECK ({this})"
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index b886d62b..062f0898 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -102,6 +102,7 @@ class Parser:
         TokenType.VAR,
         TokenType.ALTER,
         TokenType.BEGIN,
+        TokenType.BOTH,
         TokenType.BUCKET,
         TokenType.CACHE,
         TokenType.COLLATE,
@@ -124,6 +125,7 @@ class Parser:
         TokenType.ISNULL,
         TokenType.INTERVAL,
         TokenType.LAZY,
+        TokenType.LEADING,
         TokenType.LOCATION,
         TokenType.NEXT,
         TokenType.ONLY,
@@ -143,6 +145,7 @@ class Parser:
         TokenType.TABLE_FORMAT,
         TokenType.TEMPORARY,
         TokenType.TOP,
+        TokenType.TRAILING,
         TokenType.TRUNCATE,
         TokenType.TRUE,
         TokenType.UNBOUNDED,
@@ -157,6 +160,8 @@ class Parser:
         TokenType.TRY_CAST,
     }
 
+    TRIM_TYPES = {TokenType.LEADING, TokenType.TRAILING, TokenType.BOTH}
+
     FUNC_TOKENS = {
         TokenType.CONVERT,
         TokenType.CURRENT_DATE,
@@ -173,6 +178,7 @@ class Parser:
         TokenType.REPLACE,
         TokenType.ROW,
         TokenType.SUBSTRING,
+        TokenType.TRIM,
         TokenType.UNNEST,
         TokenType.VAR,
         TokenType.LEFT,
@@ -369,6 +375,7 @@ class Parser:
         TokenType.CONVERT: lambda self, _: self._parse_convert(),
         TokenType.EXTRACT: lambda self, _: self._parse_extract(),
         TokenType.SUBSTRING: lambda self, _: self._parse_substring(),
+        TokenType.TRIM: lambda self, _: self._parse_trim(),
         **{
             token_type: lambda self, token_type: self._parse_cast(
                 self.STRICT_CAST and token_type == TokenType.CAST
@@ -1937,6 +1944,34 @@ class Parser:
 
         return this
 
+    def _parse_trim(self):
+        # https://www.w3resource.com/sql/character-functions/trim.php
+        # https://docs.oracle.com/javadb/10.8.3.0/ref/rreftrimfunc.html
+
+        position = None
+        collation = None
+
+        if self._match_set(self.TRIM_TYPES):
+            position = self._prev.text.upper()
+
+        expression = self._parse_term()
+        if self._match(TokenType.FROM):
+            this = self._parse_term()
+        else:
+            this = expression
+            expression = None
+
+        if self._match(TokenType.COLLATE):
+            collation = self._parse_term()
+
+        return self.expression(
+            exp.Trim,
+            this=this,
+            position=position,
+            expression=expression,
+            collation=collation,
+        )
+
     def _parse_window(self, this, alias=False):
         if self._match(TokenType.FILTER):
             self._match_l_paren()
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index efd70236..819c6a16 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -92,6 +92,7 @@ class TokenType(AutoName):
     AUTO_INCREMENT = auto()
     BEGIN = auto()
     BETWEEN = auto()
+    BOTH = auto()
     BUCKET = auto()
     CACHE = auto()
     CALL = auto()
@@ -160,6 +161,7 @@ class TokenType(AutoName):
     JOIN = auto()
     LATERAL = auto()
     LAZY = auto()
+    LEADING = auto()
     LEFT = auto()
     LIKE = auto()
     LIMIT = auto()
@@ -218,7 +220,9 @@ class TokenType(AutoName):
     TIME = auto()
     TOP = auto()
     THEN = auto()
+    TRIM = auto()
     TRUE = auto()
+    TRAILING = auto()
     TRUNCATE = auto()
     TRY_CAST = auto()
     UNBOUNDED = auto()
@@ -376,6 +380,7 @@ class Tokenizer(metaclass=_Tokenizer):
         "AUTO_INCREMENT": TokenType.AUTO_INCREMENT,
         "BEGIN": TokenType.BEGIN,
         "BETWEEN": TokenType.BETWEEN,
+        "BOTH": TokenType.BOTH,
         "BUCKET": TokenType.BUCKET,
         "CALL": TokenType.CALL,
         "CACHE": TokenType.CACHE,
@@ -439,6 +444,7 @@ class Tokenizer(metaclass=_Tokenizer):
         "JOIN": TokenType.JOIN,
         "LATERAL": TokenType.LATERAL,
         "LAZY": TokenType.LAZY,
+        "LEADING": TokenType.LEADING,
         "LEFT": TokenType.LEFT,
         "LIKE": TokenType.LIKE,
         "LIMIT": TokenType.LIMIT,
@@ -493,6 +499,8 @@ class Tokenizer(metaclass=_Tokenizer):
         "TEMPORARY": TokenType.TEMPORARY,
         "THEN": TokenType.THEN,
         "TRUE": TokenType.TRUE,
+        "TRAILING": TokenType.TRAILING,
+        "TRIM": TokenType.TRIM,
         "TRUNCATE": TokenType.TRUNCATE,
         "TRY_CAST": TokenType.TRY_CAST,
         "UNBOUNDED": TokenType.UNBOUNDED,


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index ee0c5f58..e32e151d 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -15,6 +15,10 @@ class TestMySQL(Validator):
 
     def test_identity(self):
         self.validate_identity("SELECT CAST(`a`.`b` AS INT) FROM foo")
+        self.validate_identity("SELECT TRIM(LEADING 'bla' FROM ' XXX ')")
+        self.validate_identity("SELECT TRIM(TRAILING 'bla' FROM ' XXX ')")
+        self.validate_identity("SELECT TRIM(BOTH 'bla' FROM ' XXX ')")
+        self.validate_identity("SELECT TRIM('bla' FROM ' XXX ')")
 
     def test_introducers(self):
         self.validate_all(
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 15fe3612..9c4d4f0a 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -72,6 +72,10 @@ class TestPostgres(Validator):
         self.validate_identity(
             "SELECT * FROM x WHERE SUBSTRING('Thomas' FROM '%#\"o_a#\"_' FOR '#') IN ('mas')"
         )
+        self.validate_identity("SELECT TRIM(' X' FROM ' XXX ')")
+        self.validate_identity(
+            "SELECT TRIM(LEADING 'bla' FROM ' XXX ' COLLATE utf8_bin)"
+        )
 
         self.validate_all(
             "CREATE TABLE x (a INT SERIAL)",
@@ -124,3 +128,29 @@ class TestPostgres(Validator):
                 "spark": "SELECT * FROM x WHERE SUBSTRING(col1, 3 + LENGTH(col1) - 10, 10) IN (col2)",
             },
         )
+        self.validate_all(
+            "SELECT TRIM(BOTH ' XXX ')",
+            write={
+                "mysql": "SELECT TRIM(' XXX ')",
+                "postgres": "SELECT TRIM(' XXX ')",
+                "hive": "SELECT TRIM(' XXX ')",
+            },
+        )
+        self.validate_all(
+            "TRIM(LEADING FROM ' XXX ')",
+            write={
+                "mysql": "LTRIM(' XXX ')",
+                "postgres": "LTRIM(' XXX ')",
+                "hive": "LTRIM(' XXX ')",
+                "presto": "LTRIM(' XXX ')",
+            },
+        )
+        self.validate_all(
+            "TRIM(TRAILING FROM ' XXX ')",
+            write={
+                "mysql": "RTRIM(' XXX ')",
+                "postgres": "RTRIM(' XXX ')",
+                "hive": "RTRIM(' XXX ')",
+                "presto": "RTRIM(' XXX ')",
+            },
+        )
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index eaef0229..946e5531 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -334,6 +334,7 @@ class TestExpressions(unittest.TestCase):
         self.assertIsInstance(parse_one("TIME_STR_TO_DATE(a)"), exp.TimeStrToDate)
         self.assertIsInstance(parse_one("TIME_STR_TO_TIME(a)"), exp.TimeStrToTime)
         self.assertIsInstance(parse_one("TIME_STR_TO_UNIX(a)"), exp.TimeStrToUnix)
+        self.assertIsInstance(parse_one("TRIM(LEADING 'b' FROM 'bla')"), exp.Trim)
         self.assertIsInstance(parse_one("TS_OR_DS_ADD(a, 1, 'day')"), exp.TsOrDsAdd)
         self.assertIsInstance(parse_one("TS_OR_DS_TO_DATE(a)"), exp.TsOrDsToDate)
         self.assertIsInstance(parse_one("TS_OR_DS_TO_DATE_STR(a)"), exp.Substring)