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

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


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.