CODEWITHSUNDEEP
stringoracle-databasesorting
Vivek
Vivek

Reputation: 4642

Using MAX() in VARCHAR Field

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?

Upvotes: 14

Views: 54691

Answers (6)

Muhammad ali
Muhammad ali

Reputation: 297

SELECT * 
FROM  `<TABLE_NAME>` 
ORDER BY CAST(  `ID` AS DECIMAL( 10, 3 ) ) DESC

Upvotes: 1

BSB
BSB

Reputation: 2468

First Varchar need to Casted as int to select as MAX. Use below query:

select max(CAST(ID as signed)) as max_id from <table-name>;

Upvotes: 0

Ketan Dubey
Ketan Dubey

Reputation: 430

This will surely work.

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

Upvotes: 0

Santhosh
Santhosh

Reputation: 113

below code is working for me as per your expectation 

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

Upvotes: 5

Mohamed Islam Fares
Mohamed Islam Fares

Reputation: 193

this should work

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

Upvotes: 0

Thomas
Thomas

Reputation: 64635

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.

Upvotes: 32

Related Questions