Sorting a varchar column as integer value in Oracle query

You can do this with the logic in the order by:

order by (case when regexp_like(door, '^[0-9]*$') = 0 then 1 else 0 end) desc,
         (case when regexp_like(door, '^[0-9]*$') = 0 then door end),
         length(door),
         door

This first puts the non-numeric values first. The second clauses sorts these alphabetically. The third and fourth are for numbers. By sorting for the length before the value, you will get the numbers in order.

ORA-01722 error coming because of value 'a' ,'b', Go for custom function which will take varchar and return number to convert , use custom function in order by clause of your query.

CREATE OR REPLACE FUNCTION tonumber (no_str varchar2)
        RETURN number IS
           num number := 0;
        BEGIN
           RETURN to_number(no_str);
        EXCEPTION  -- exception handlers begin A < B < 1 < 2
           WHEN value_error THEN  -- handles all other errors
               dbms_output.put_line('in other exception catch.');
               CASE
                  WHEN ( upper(no_str) = 'B' )  THEN  return -1;
                  WHEN ( upper(no_str) ='A') THEN return -2;
                  ELSE return -999;
               END CASE;
        END;

Add when condition as in required. now assumed it can have only A B. for rest it will return default.

(This approach assumes that there aren't any mixed values like "234abc567".)

So, going old school...just 0-pad the strings to the maximum length of the column so that they'll sort properly as characters. But, to get the "non-numeric" values to sort first, nullify non-numeric values, put the NULLs first and padded values after that.

select door
  from address
 order by case when replace(translate(door, '012345679', '0000000000'), '0', '') is null 
               then lpad(door, 10, '0') -- value was entirely made of digits (change the 10 to the max width of the column)
               else null 
           end nulls first
        , door -- sorting within the group of NULL rows generated in the previous expression.

use the below query

  SELECT PUMP_NAME 
    FROM MASTER.PUMPS
ORDER BY LPAD(PUMP_NAME, 10);