CODEWITHSUNDEEP
sqloracle-database
Lokomotywa
Lokomotywa

Reputation: 2844

Oracle-SQL Query to obtain the maximum value of a column that has been converted from String to Integer

In a table within my database there is a varchar2 column that mostly consists of numbers. Now I want to receive the NUMERICAL maximum of that column, ignoring non-numerical values. How do I do that? Thank you.

Upvotes: 0

Views: 769

Answers (3)

Dmitry Nikiforov
Dmitry Nikiforov

Reputation: 3038

SQL> select * from t;

X                                                                               
----------                                                                      
1234                                                                            
836                                                                             
836AA%%$$                                                                       
BBcdfrg                                                                         
12099                                                                           

SQL> select max(to_number(x)) from t
  2  where trim(translate(x,'0123456789',' ')) is null
  3  /

MAX(TO_NUMBER(X))                                                               
-----------------                                                               
            12099

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

There are so many traps and exceptions when you try to convert a string into a number. I use a function like this:

FUNCTION Is_Numeric(Expression IN VARCHAR2) RETURN INTEGER
BEGIN
   IF TO_NUMBER(Expression) IS NOT NULL THEN
      RETURN 1;
   ELSE
      RETURN 0;   
   END IF;    
EXCEPTION
   WHEN VALUE_ERROR OR INVALID_NUMBER THEN
      RETURN 0; 
END Is_Numeric;

Then you can use it in any WHERE condition.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You need to determine if the number is numeric. Here is one way for positive integers:

select max(case when LENGTH(TRIM(TRANSLATE(string1, ' 0123456789', ' '))) is null
                then cast(string1 as integer)
           end)
. . .

If you have other numeric formats (floating point, negative values, exponential), then appropriate tests can be put in for those.

By the way, the web is filled with advice to use something like:

LENGTH(TRIM(TRANSLATE(myfield, ' +-.0123456789', ' '))) is null

Be careful, because strings such as '1+1' and '...' would pass this test.

Upvotes: 1

Related Questions