Sort coulmn with alphanumeric values in a table

In fact, why not just sort on the substring expressions?

order by substr(chrcol,9,4), /* your year */
    substr(chrcol,1,1) /* your Q number */

which would work as long as you kept to that format or we reach the year 10,000.

Update:

While I'm thinking about it, I should add that you could also sort on a CASE expression.

Here's an example:

create table annsales
    (quarter varchar2(10),
    year int,
    revenue float);

insert INTO annsales values ('First',2009,26.0);
insert INTO annsales values ('Second',2009,29.0);
insert INTO annsales values ('Third',2009,30.0);
insert INTO annsales values ('Fourth',2009,29.0);
insert INTO annsales values ('First',2008,20.0);
insert INTO annsales values ('Second',2008,25.0);
insert INTO annsales values ('Third',2008,24.0);
insert INTO annsales values ('Fourth',2008,27.0);

Now, this probably doesn't produce rows in the order you want:

select  *
from    annsales
order by year, quarter;

But this does order by quarter, as opposed to the name of the quarter:

select  *
from    annsales
order by year,
    case Quarter
      when 'First' then 1
      when 'Second' then 2
      when 'Third' then 3
      when 'Fourth' then 4
      END;

If you did have that ordering problem, it might be worthwhile to have a function "Ordinal_Text_to_Int" that handles the cases to tenth place for you ('First', 'Second',... 'Tenth').

I usually use a technique like this when we have a bunch of status codes ('A','X','E','C'... whatever) and I want to see the output sorted with the most critical status ('E' for error, maybe) first.

case STATUS
  when 'E' then 0
  when 'A' then 1
  when 'X' then 2
...
  else -1 end /* unkown statuses shouldn't happen, put them ahead of E's */

CREATE TABLE t1 (chrcol VARCHAR(20),data NUMBER)
INSERT INTO t1 VALUES('1st Qtr 2008',50)
INSERT INTO t1 VALUES('2nd Qtr 2008',40)
INSERT INTO t1 VALUES('3rd Qtr 2008',30)
INSERT INTO t1 VALUES('4th Qtr 2008',20)
INSERT INTO t1 VALUES('1st Qtr 2009',30)
INSERT INTO t1 VALUES('2nd Qtr 2009',40)
INSERT INTO t1 VALUES('3rd Qtr 2009',50)
INSERT INTO t1 VALUES('4th Qtr 2009',60)


 SELECT chrcol,data FROM 
     (SELECT chrcol,data,substr(chrcol,1,7) AS quarter, 
             substr(chrcol,8,5) AS year FROM t1) 
 ORDER BY year,quarter

(I haven't got an Oracle database to hand to test this, so there may be some minor error in syntax, but the approach should be obvious)

You could also use a function.

CREATE OR replace FUNCTION my_sort_order
(qtr_expression VARCHAR2)
RETURN VARCHAR2 DETERMINISTIC IS
V_RETVAL VARCHAR2(5);
BEGIN
V_RETVAL := substr(qtr_expression,9,4) || substr(qtr_expression,1,1);
RETURN V_RETVAL;
END my_sort_order;

CREATE TABLE t1 (chrcol VARCHAR(20),data NUMBER)       ;
INSERT INTO t1 VALUES('1st Qtr 2008',50) ;
INSERT INTO t1 VALUES('2nd Qtr 2008',40) ;
INSERT INTO t1 VALUES('3rd Qtr 2008',30) ;
INSERT INTO t1 VALUES('4th Qtr 2008',20) ;
INSERT INTO t1 VALUES('1st Qtr 2009',30) ;
INSERT INTO t1 VALUES('2nd Qtr 2009',40);
INSERT INTO t1 VALUES('3rd Qtr 2009',50) ;
INSERT INTO t1 VALUES('4th Qtr 2009',60);

SELECT * FROM t1
ORDER BY my_sort_order(chrcol);

This worked but I had to re-edit it to post - may require further checking.