Query to Change Quarter Timestamp Format

First of all (and as usual), you should not store DATE values as VARCHAR, even if you need only the quarter.

You can do following. First convert the string into a DATE, e.g.

SELECT
    TO_DATE(REGEXP_SUBSTR('Q2 FY07', '\d{2}$')||'-'||REGEXP_SUBSTR('Q2 FY07','\d')*3, 'RR-MM')
FROM ...

Then you can change the output format according to your needs, i.e.

TO_CHAR(
    TO_DATE(REGEXP_SUBSTR('Q2 FY07', '\d{2}$')||'-'||REGEXP_SUBSTR('Q2 FY07','\d')*3, 'RR-MM')
    , 'YYYY"Q"Q')