Remove a certain string (hint) from SQL code in oracle

Question

I have a string (clob) that contains SQL query with hints, i need to remove those hints from the sql code.

so this:

select 
/*+ ALL_ROWS */ 
/* 2014-12-08 08:26:40.533 -6e99e394:14a2a127782:-466(TXN_ID:-1) */ 
ID, ORIGIN_TICKET_ID, LIFECYCLE_STATUS ... 

should looks like:

select ID, ORIGIN_TICKET_ID, LIFECYCLE_STATUS ... 

I tried to play with replace and REGEXP_REPLACE but with no luck so far.

Answer 1

Edited to handle the CLOB…

Something along

SELECT 'SELECT '
  || TRIM(SUBSTR(str, LENGTH(str) - INSTR(REVERSE(str), '/*') + 2)) cleanedStatement
FROM
  (SELECT DBMS_LOB.SUBSTR(main.c, 4000, 1) str FROM Main
  );

should provide the requested as long as the comments (hints or not) are all placed at the statements' start.