PiC PiC - 2 months ago 41
SQL Question

Oracle RTRIM trims more than asked for

Does anyone know why these:

SELECT RTRIM('123R_CLUSTER', '_CLUSTER') -- should give '123R'
SELECT RTRIM('123S_CLUSTER', '_CLUSTER') -- should give '123S'
SELECT RTRIM('123T_CLUSTER', '_CLUSTER') -- should give '123T'
SELECT RTRIM('123U_CLUSTER', '_CLUSTER') -- should give '123U'

return '123' instead of the expected?

I'm on Oracle Database 12c Enterprise Edition Release - 64bit Production.

The fun begins when you try these:

  • replace 123 with anything else (no change still wrong results, i.e.
    trims one character more),

  • replace "R" / "S" / "T" / "U" with anything else, (works OK)

  • replace "_CLUSTER" with anything else, (works OK)

  • add anything after "_CLUSTER" (no change).


The documentation is quite clear:

The Oracle/PLSQL RTRIM function removes all specified characters from the right-hand side of a string.

So it doesn't remove the string _CLUSTER at the end of your string - it removes characters until there is one that isn't _, C, L, U, S, T, E or R. Since your postfixes are R/S/T/U, they also match the rtrim condition, and are removed. As would 123S_SLURTE, for example.

As an easier to understand example,

rtrim('LK_123aababaabbbababbaa', 'ab') // returns LK_123

rtrim simply isn't the tool for the job at hand :)