sql order by numeric string .

Question

I am using oracle 10. I need to sort my result set according to two numeric string fields. one sort criterion field holds data like this: FIELD1:

FO-100001001001 
FO-100001002001
FO-100001003001
SQ-200001003001
FC-102001003001

the other :

FIELD2:
000203
000567
349990

I need to combine the two criterion , the first criterion take the priority , the result needs an ascending order.

How do I write this sql ?

Answer 1

Since the numbers are zero-padded, you can simply compare them as strings:

SELECT ...
FROM ...
ORDER BY field1 ASC, field2 ASC

Or if you want to ignore the prefix in field1:

SELECT ..., SUBSTR(field1, 3) AS stripped_field1 
FROM ...
ORDER BY stripped_field1 ASC, field2 ASC

Answer 2

I am assuming that by "numeric string", you mean "varchar", and that alpha-numeric sorting works for you (which it should if the format is fixed and you have the leading zeroes).

select * from table order by field1, field2;