ORACLE TRIM and RTRIM: the TRIM (TRAILING… ) select with more than one character?

Question

Why has the lette ’m’ special role in following query? I tried to create a TRIM functions and I am stuck with it. Or: what is the differnce when I query from the dual and another table.

E.g.

I created a table with one column and two values (’adam’ and ’apperda’)

create table x
(col1 varchar2(20));
insert into x values ('adam');
insert into x values ('apperda');

Then I select the TRIM(TRAILING… ) and the RTRIM as follows:

select 
col1,
trim(trailing 'am' from col1) traling,
rtrim(col1, 'am') rtim
from x;

And the result: ’ad’ and ’apperd’

COL1                 TRALING              RTIM                
-------------------- -------------------- --------------------
adam                 ad                   ad                  
apperda              apperd               apperd              

I don’t understand why apperd turns up in the result… It should be an error message, shouldn’t it: ORA-30001: trim set should have only one character?

I tried two other selects:

1. Removed the trim(trailing…) part of the select.

   select
   col1,
   trim(trailing 'am' from col1) traling
   from x;
   

2. Select from dual

   select
   trim(trailing 'am' from 'apperda'),
   from dual;
   

Both query give me the error message:

ORA-30001: trim set should have only one character

The plus row in a query provides plus information? Or how it is possible? And it is not just the a query question but seems the two data (adam and apperda) interact:

I create a simple query with apperda and it show the error:

select 
trim(trailing 'am' from 'apperda') traling,
rtrim('apperda', 'am') rtim
from dual

ORA-30001: trim set should have only one character

---

the core example (2018-07-05)

create table trims
(col varchar2(20));

insert into trims values ('dream');

select
    trim(trailing 'am' from col)
    rtrim(col, 'am')
from trims;

The first row in the select should give the error ORA-30001, because there are two characters in trim(trailing...). But the script runs:

TRIM(TRAILING'AM'FROMCOL)   RTRIM(COL,'AM')
-----------------------------------------------
dre                         dre

And: it seems that the trim(trailin...) depends on the rtrim(...) - if you erase the rtrim(...), the error message appears as is should be.

select
    trim(trailing 'am' from col)
    --rtrim(col, 'am')
from trims;

`ORA-30001: trim set should have only one character`

Answer 1

I'm wondering if this is a side-effect of some kernel optimisation. Effectively trim(trailing 'am' from col) and rtrim(col, 'am') are the same operation. So it seems that if we execute rtrim(col, 'am') second Oracle doesn't bother trim(trailing 'am' from col) but simply returns the result of the rtrim() instead.

Hence while these queries returns the trimmed results ...

select
    trim(trailing 'am' from col)
    , rtrim(col, 'am')
from trims;
/

select
    trim(trailing 'ax' from col)
    , rtrim(col, 'ax')
from trims;
/

... switching the order of execution hurls ORA-30001: trim set should have only one character:

select
    rtrim(col, 'am')
    , trim(trailing 'am' from col)
from trims;
/

So does providing different values to trim:

select
    trim(trailing 'am' from col)
    , rtrim(col, 'ax')
from trims;
/

The one snag with this theory is that Oracle executes functions running left-to-right. So it's peculiar that Oracle hurls when the invalid trim() is the rightmost function call in the query when we would expect Oracle to object when it's the first function to be executed.