Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Proposal - add an optional argument c after a comma in the TRIM(s) function

iwisNov 24 2020 — edited Apr 15 2021

Hi!
I compared string functions in 5 different RDBMS: PostgreSQL, MySQL, Oracle, SQL Server and SQLite, and also in the ANSI SQL Standard. There are large differences among different RDBMS, and I propose to reduce them a little bit.
Particularly, I propose to add an optional argument c after a comma in the TRIM(s) function (the space ' ' by default).
Explanation: We can now call the following functions in Oracle:

LTRIM(s [,c])
RTRIM(s [,c])
TRIM (s)

As you see, we can pass the argument c after the comma in the LTRIM and RTRIM functions, but not in the TRIM function, which is strange. For the TRIM function we have to use the SQL ANSI Standard syntax: TRIM(c FROM s). On the other hand, in PostgreSQL and SQLite calling TRIM(s, c) is possible. So this difference between different RDBMS and the difference in Oracle itself between the LTRIM/RTRIM(s [,c]) and TRIM(s) functions seem to be unjustified. Or maybe somebody knows a justification?
I also proposed this change: https://community.oracle.com/tech/developers/discussion/4477919/proposal-add-the-substring-alias-for-the-substr-function/.
The changes proposed by me for other RDBMS are listed here: https://github.com/iwis/SQL-notes/blob/master/Functions_proposed_changes.md

Comments

Mike Kutz

Did you check to see if the syntax is valid already?

select
   trim( '01234567890', '0') x
from dual,
iwis

Of course I checked it. Both Oracle 19c on https://livesql.oracle.com/ and Oracle 18c on https://dbfiddle.uk/?rdbms=oracle_18&fiddle=5294d7089cb5552ca2fdf89c62e9bab4 raises the ORA-00907: missing right parenthesis error.

iwis

I have added this proposal in the "ideas" section: https://community.oracle.com/tech/developers/discussion/4479151/add-an-optional-argument-c-after-a-comma-in-the-trim-s-function/p1?new=1

Solomon Yakobson

You said you compared different database engines:
Oracle trim('0' from '01234567890')
SQL Server trim('0' from '01234567890')
MySQL trim('0' from '01234567890')
Postgres trim('0' from '01234567890')
All the above database engines have same TRIM syntax allowing leading, trailing or both (default):
TRIM([LEADING | TRAILING | BOTH] [characters] FROM string)
SY.

iwis

All the above database engines have same TRIM syntax allowing leading, trailing or both (default):
TRIM([LEADING | TRAILING | BOTH] [characters] FROM string)
Unfortunately, this syntax doesn't work in SQL Server. The select TRIM(LEADING '0' FROM '01230') query raises:

Msg 102 Level 15 State 1 Line 1
Incorrect syntax near '0'.

See: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9f1441351df23937f8866eb4edb32bd9

Solomon Yakobson

Right, SQL Server TRIM trims on both sides only (we must use LTRIM/RTRIM to trim on one side). But what this has to do with your proposal? Neither of database engines you listed except SQLite supports TRIM('string','characters') syntax so it should be SQLite where TRIM([LEADING | TRAILING | BOTH] [characters] FROM string) syntax should be proposed.
SY.

iwis

Neither of database engines you listed except SQLite supports TRIM('string','characters') syntax
PostgreSQL also supports it (db<>fiddle). Additionally, as I said, it looks strange that Oracle database supports LTRIM(s, c) and RTRIM(s, c), but doesn't support: TRIM(s, c).
it should be SQLite where TRIM([LEADING | TRAILING | BOTH] [characters] FROM string) syntax should be proposed.
It should be proposed in SQLite and SQL Server. I have done it here and here.

Solomon Yakobson

It should be proposed in SQLite and SQL Server. I have done it here and here.
Good, so when SQL Server & SQLite will implement it then TRIM([LEADING | TRAILING | BOTH] [characters] FROM string) will be fully portable.
SY.

1 person found this helpful
1 - 8

Post Details

Added on Nov 24 2020
#general-database-discussions
8 comments
198 views