I want to take the max value of the substring of a varchar field having values as SN1,SN2....SN10.I used the query as following

select max(substring(st_no,3,4)) from tbl_st;

I am getting the max value as 9.I want to get 10.

If somebody knows please help me

Since they are strings, 9 comes after 1. You'll have to convert them to numbers if you want numerical sort order ( cast(substring(st_no,3,4) AS INT) ), assuming they are all numbers. Since they also have string part, you'll have to split them into two fields ordering first on the text, then on the numeric part.