how to find max(varchar field) using sql server

• swlakshmi

  Old Hand

  Points: 309

  More actions

  September 17, 2009 at 12:59 am

  #204694

  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.

• lionofdezert

  SSC Enthusiast

  Points: 104

  More actions

  September 17, 2009 at 3:33 am

  #1054862

  SELECT MAX(CAST(SUBSTRING(bookid,3, LEN(bookid)) AS INT)) from tablename

• Apurva

  SSCrazy

  Points: 2412

  More actions

  September 18, 2009 at 4:05 pm

  #1055895

  You can skip the len(bookid) part by lion

  SELECT MAX(convert(INT,SUBSTRING(bookid,3,8))) FROM tablename

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  September 21, 2009 at 12:58 am

  #1056105

  Its better not to script the LEN part . It is there in case the length of the bookid varies. The length of bookid could be less than or greater than 8. Skip that only if the length is fixed as 8.

  "Keep Trying"