Using MAX() in VARCHAR Field

Question

I have a table with following set of data

ID (VARCHAR2 field)
D001
D002
D010
D0012

I use max() in this field.

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

It returns D010 as result.

Why is the result not D0012?

Answer 1

You get D010 because alphabetically, D010 comes after D0012 or said another way, D01 comes after D00 and therefore anything that is D01x comes after anything that starts D00x.

Answer 2

below code is working for me as per your expectation

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