ORACLE Obtain the greater and lower value in a varchar2 field range

Question

I have a table with a field varchar2 type,

Suppose this values:

aaab
s123
2445
25
21000
2500000
1
10000790
1899

I need to obtain the greater value that begins with 2.

I was trying with:

Select TO_NUMBER(myfield) from Services where myfield like '2%';

I get

2445
25
21000
2500000

Now, I want to obtain, the greater and the smaller values 25 and 2500000.

I was trying with:

Select TO_NUMBER(myfield) from Services where myfield like '2%' ORDER BY myfield DESC;

and

Select MAX(TO_NUMBER(myfield)) from Services where myfield like '2%';

Select MIN(TO_NUMBER(myfield)) from Services where myfield like '2%';

I get:

01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

Answer 1

Oracle executes most function calls regardless if the row is part of the result set or not. To ensure that the function is only called for values of the result set put the where condition in a subquery:

Select TO_NUMBER(myfield) 
  from (SELECT * from Services where myfield like '2%')
 ORDER BY 1 DESC

If you are not sure, that every value of your table is a number it would be better to write your own pl/sql function:

CREATE FUNCTION my_to_number(val IN VARCHAR2) RETURN NUMBER IS
BEGIN 
  RETURN TO_NUMBER(val);
EXCEPTION
  WHEN VALUE_ERROR THEN 
    RETURN NULL;
END;

And then use this function to get your values:

Select MY_TO_NUMBER(myfield) 
  from Services where myfield like '2%'
 ORDER BY 1 DESC