Using MAX () in a VARCHAR field

Question

I have a table with the following dataset

ID (VARCHAR2 field)
D001
D002
D010
D0012

I use max()in this field.

Select max(ID) from <table-name>;

As a result, returns D010.

Why is the result not D0012?

Answer 1

You get D010it because it D010appears in alphabetical order after D0012or in another way, D01appears after D00, and therefore everything that is D01xcomes after everything that begins with D00x.

Answer 2

below code works for me according to your expectation

select max(to_number(regexp_substr(id, '\d+'))) id from <yourtable>;

Answer 3

, , .

    select MAX(CAST(REPLACE(REPLACE(ID, 'D', ''), '', '') as int)) from <table-name>

Answer 4

it should work

Select MAX(ID) from table where IsNumeric(ID) = 1 ;