Trimming specific characters from a string in Oracle SQL

Problem:
I am trying to trim specific characters (_CMP) from the string only if they occur at the very end of the string, i.e.


'XXX_CMP' should return 'XXX'

'XXXCMP' ---> 'XXXCMP'

'CMP_CMP' ---> 'CMP'

'_CMP_CMP' ---> '_CMP'

'_CMP' ---> NULL


I am trying to use any rtrim, instr (with substr), but can not handle ALL cases.

Am I missing something? Is there another sql function that might help?

Answer:
Try decode ( INSTR(value_ to_trim, '_CMP', -1,1),

0, value_to_trim,

SUBSTR (value_to_trim, 1, INSTR(value_ to_trim, '_CMP', -1,1)-1) )


If the INSTR returns 0 then the string to trim is not there so DECODE will return the original.

IF INSTR does not return a 0 then there is a string to trim so DECODE returns the SUBSTR

select
decode ( INSTR(:value_ to_trim, '_CMP', -1,1),
0, :value_to_trim,
SUBSTR (:value_to_trim, 1, INSTR(:value_ to_trim, '_CMP', -1,1)-1) )
from dual;

for :value_to_trim = '_CMP_CMPCMP'

The following two analogous snippets of code will always trim _CMP from the end of a string (and they return a null string when :str is exactly equal to _CMP) and not change the string otherwise (and they don't use the INSTR function):


SELECT CASE WHEN SUBSTR(:str ,-4) = '_CMP'
THEN SUBSTR(:str ,1 ,LENGTH(:str) - 4)
ELSE :str END
FROM dual ;


SELECT DECODE(SUBSTR(:str ,-4)
,'_CMP' ,SUBSTR(:str ,1 ,LENGTH(:str) - 4)
,:str)
FROM dual ;