how to find max(varchar field) using sql server

• swlakshmi

  Old Hand

  Points: 309

  More actions

  September 17, 2009 at 12:59 am

  #210936

  Hi,

  I am using SQL Server , I have a field name bookid of type varchar.

  Example data for this field is given below:

  BK1

  BK2

  BK10

  BK11

  I am using select substring(max(bookid),3,8)) from tablename to get the max value of Bookid field.

  But, the result is : BK2.

  Please help me in resolving this issue. I cannot change the datatype of the bookid. Give me a perfect query to get the max value of that varchar field.

  Thanks.

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  September 17, 2009 at 3:53 am

  #1054870

  The query you posted gives a syntax error... but I see what you are trying to do.

  The reason is is returning 2 is because you are looking for the MAX of a string, not a number. Try something like this:-

  select max(convert(int,substring(bookid,3,8))) from tablename