Bug #101719 Add an optional argument c after a comma in the [L|R]TRIM(s) functions
Submitted: 23 Nov 2020 13:41 Modified: 23 Nov 2020 14:05
Reporter: Dariusz Dacko Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[23 Nov 2020 13:41] Dariusz Dacko
Description:
I propose to add an optional argument c after a comma in the TRIM(s), LTRIM(s), and RTRIM(s) functions (the space ' ' by default).

Explanation: Currently, we can call the following functions in MySQL: TRIM(s), LTRIM(s), RTRIM(s). In PostgreSQL and SQLite we can also call TRIM(s, c), LTRIM(s, c), and RTRIM(s, c).

Scalar SQL functions differ quite significantly among RDBMS. This step will reduce this difference. Other changes in RDBMS proposed by me are here: https://github.com/iwis/Scalar-SQL-functions/blob/main/Proposed_changes.md

How to repeat:
The SQL ANSI Standard function:
  select TRIM('-' FROM '--abc--');
returns 'abc'.

But this function:
  select TRIM('--abc--', '-');
raises:
  "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', '-')' at line 1".

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2c5776ef52c8f4a3e58a768f6a11880e

Suggested fix:
Add an optional argument c after a comma in the TRIM(s), LTRIM(s), and RTRIM(s) functions (the space ' ' by default).
[23 Nov 2020 14:05] MySQL Verification Team
Hello Dariusz,

Thank you for the feature request.

regards,
Umesh
[6 Dec 2023 21:42] MySQL Verification Team
Now  LTRIM(s, c) and RTRIM(s, c) became SQL Standard (SQL:2023), so it's time to rethink to improve our implementation.

Current status:
LTRIM(s), RTRIM(s) : Firebird, MySQL, IBM Db2(9.7 or earlier), SQL Server(2019 or earlier)
LTRIM(s,c), RTRIM(s,c): Oracle DB, PostgreSQL, SQLite, IBM Db2(10.1 or later), SQL Server(2022 or later)
[6 Dec 2023 21:43] MySQL Verification Team
[SQL:2023]
https://peter.eisentraut.org/blog/2023/04/04/sql-2023-is-finished-here-is-whats-new