Q: ORACLE SQL: Shortest query to remove last group of bracket Info: I use Oracle 11g
Lets say this is sample data:
Lemon(USD4)(RM16)(SG8)
Melon(RM40)(SG20)(USD10)(EU5)
Orange(USD7)(RM28)
Apple(USD2)
Grape

I want to replace the last bracket group (if any) to produce:
Lemon(USD4)(RM16)
Melon(RM40)(SG20)(USD10)
Orange(USD7)
Apple
Grape

 
These are two of my SQL which seem too long:
SELECT
    CASE
        WHEN REGEXP_COUNT(FOOD, '\(' ) = 0 THEN FOOD
        ELSE SUBSTR(FOOD, 1, INSTR(FOOD, '(', -1)-1)
    END
FROM TABLE_A;

SELECT
    DECODE(
        REGEXP_COUNT(FOOD, '\(' ), 0,
        FOOD,
        SUBSTR(FOOD, 1, INSTR(FOOD, '(', -1)-1)
    )
FROM TABLE_A;

 
I seek shorter. Probably something like this:
SELECT REGEXP_REPLACE(FOOD, '\(what_is_here\)', '', 1, 2) FROM TABLE_A;


A: Try \([^\)]*\)$ pattern:
SELECT t.*,
  REGEXP_REPLACE( food, '\([^\)]*\)$', '' ) as xxx
from table1 t

Live demo:http://sqlfiddle.com/#!4/1188b/19
|                          FOOD |                      XXX |
|-------------------------------|--------------------------|
|        Lemon(USD4)(RM16)(SG8) |        Lemon(USD4)(RM16) |
| Melon(RM40)(SG20)(USD10)(EU5) | Melon(RM40)(SG20)(USD10) |
|            Orange(USD7)(RM28) |             Orange(USD7) |
|                   Apple(USD2) |                    Apple |
|                         Grape |                    Grape |

