how to find max(varchar field) using sql server

• swlakshmi

  Old Hand

  Points: 309

  More actions

  September 17, 2009 at 12:57 am

  #385379

  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.

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  September 17, 2009 at 9:11 am

  #1055050

  Edit: Just found duplicate post here. Please don't post duplicates. I wouldn't have wasted my time answering if I had seen an answer already.

  Can you better define what is in the column? Are all the values in the column prefixed with "BK" or are there other possible prefixes? If all the columns are prefixed with "BK" then you could do something like this:

  DECLARE @table TABLE(value VARCHAR(11))

  INSERT INTO @table (

  value

  )

  SELECT

  'BK1'

  UNION ALL

  SELECT

  'BK2'

  UNION ALL

  SELECT

  'BK10'

  UNION ALL

  SELECT

  'BK11'

  SELECT

  *

  FROM

  @TABLE T

  WHERE

  EXISTS (

  SELECT 1

  FROM

  @TABLE T2

  HAVING

  MAX(CONVERT(INT, SUBSTRING(T2.value, 3, 8))) = CONVERT(INT, SUBSTRING(T.value, 3, 8))

  )

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question