Sorting alphanumeric columns in sql server 7

• ml_ycmahesh

  Valued Member

  Points: 73

  More actions

  November 12, 2002 at 8:55 am

  #79260

  Hello,

  I have a column in the database which contans alpha numeric data as follows.

  Column

  ------

  AAA

  AA-

  B+

  B-

  AA

  AAA+

  AA+

  BBB

  BBB-

  BB

  AAA-

  A+

  A-

  A

  I want to write a query to sort this data as per the rating priority.

  When I sort the data above, I want to see the results like this.

  AAA+

  AAA

  AAA-

  AA+

  AA

  AA-

  A+

  A

  A-

  BBB

  BBB-

  BB

  B+

  B

  B-

  I was wondering if I could do this without using any additional temp tables.I already have some temp tables/cursors which I'm using in my stored procs. I donot want to add more overhead.

  Currently I store the comma separated list of columns to retrieve,where clauses and order by clause as some columns in the database. I concatenate these fields based on the view from where I retrieve the data and execute a dynamic sql to get the data.

  If I want to sort the data by the column above then I only want to change the order by clause which is stored as a column.

  Let me know if this is possible in Sql server. I'm using sql server 7.0.

  Thanks and look forward to your early response.

  -Mahesha

• paulhumphris

  Hall of Fame

  Points: 3496

  More actions

  November 12, 2002 at 9:15 am

  #441136

  I think this should work, even if it isnt the most elegent solution = you just have to ignore the sequence column in the results.

  select grade,

  case ascii(right(grade, 1))

  when 43 then 1

  when 45 then 3

  else 2

  end as sequence

  from @grade

  order by left(grade, 1),

  case ascii(right(grade, 1))

  when 43 then left(grade, len(grade) - 1)

  when 45 then left(grade, len(grade) - 1)

  else grade

  end desc, sequence