CODEWITHSUNDEEP
sqloracle-database
Patrick J Abare II
Patrick J Abare II

Reputation: 1129

Oracle SQL MAX function not returning max on VARCHAR to NUMBER conversion

I am attempting to get the highest system number from a set of rows. The system number is preceded with SYS, thus select SYSTEM_ID from TABLE would yield, {SYS901,SYS87,SYS3024.....}

This is the query I'm attempting to use:

select MAX(REPLACE(SYSTEM_ID,'SYS','')) from TABLE

The possible results are

{901,87,3024,20,1}

It is returning the 901 value where I'm expecting to see the 3024 value. I assume the problem is that the field is a VARCHAR not a NUMBER. How to address this problem, I do not know.

Upvotes: 2

Views: 6838

Answers (3)

David Faber
David Faber

Reputation: 12486

You may want to consider doing a "safe" number conversion in case you have values in your table like 'SYS5001B':

SELECT MAX( TO_NUMBER( COALESCE( REGEXP_SUBSTR( system_id, '\d+' ), '0' ) ) )
  FROM table

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269613

Of course it is returning the max. It is just returning the max according to the rules of string comparisons, not numbers.

To get the numeric max, do a conversion:

select MAX(TO_NUMBER(REPLACE(SYSTEM_ID, 'SYS', ''))) from TABLE

Upvotes: 6

Multisync
Multisync

Reputation: 8797

select MAX(TO_NUMBER(REPLACE(SYSTEM_ID,'SYS',''))) from TABLE;

Use TO_NUMBER to convert VARCHAR2 to NUMBER otherwise Oracle compares strings using their ASCII codes ('9' > '3')

Upvotes: 11

Related Questions