how to get max value?

[seahorse123]

In the table, there is a column (col varchar 20).
I have these values saved:
1_1
1_2
1_9
1_14

now I want to get the max value "1_14", if the column is INT type, I can easily use MAX(col), but it's the varchar type, if I use MAX(col), I will get "1_9", so how to get the max value in the varchar type? thanks.

 

[r937]

select savedvalue
  from yourtable
 where 0+trim(leading '1_' from savedvalue)
  = ( select max(
       0+trim(leading '1_' from savedvalue)
                )
       from yourtable )

untested

r937.com | rudy.ca