how to convert varchar into integer type in sql

[shashikala]

Hi all,
i have two database enteries: 9shashi and 10shashi. both are of varchar type. how should i get maximum integer from these two values i.e,10.
If i use the query select max(id) from tablename; i am getting 9shashi. But i want to retrieve 10shashi out of there.
Please help me.
For help, i am using oracle 10g database express edition and not 'mysql'.

[buy domain]

Select Cast([Column1] as INT) 
 

[rski]

This is because for strings, max is taken in a lexicographical order.
You have to remove non-number characters and use to_number

select max(to_number(regexp_replace(column_name,'[[:alpha:]]', ''))) from test;
 

[shashikala]

thankyou both for answering. :) well rski method works smoothly. but i have another question, what does [[:alpha:]] does in the query?

[debasisdas]

For that you need to understand Regular Expression.

[rski]

See here
http://psoug.org/reference/regexp.html