Q:

Sql Server - How to sort other than alphanumeric

In Sql Server 2008 I have a DB table, and the data includes a field that indicates a time period in the form: 2d, 1w, 4y, where "d" = day, "w" = week, "y" = year.  What is the best way to sort based on this field so that they are in time order (e.g. "3w" before "1y")?
Thanks

A:

I guess you're lucky that d is before w and w is also before y so you could do
ORDER BY RIGHT(MyField, 1), CONVERT(int, LEFT(Field, LEN(Field) - 1))
However this is not going to put 1w before 8d for example.
So an alternative (more complicated) solution could be 
ORDER BY 
   CASE RIGHT(Field, 1)
       WHEN 'd' THEN LEFT(Field, LEN(Field) - 1)
       WHEN 'w' THEN 7*LEFT(Field, LEN(Field) - 1)
       ELSE 356 * LEFT(Field, LEN(Field) - 1)
   END 

It makes assumptions about your data (e.g. only d, w, and y are going to be there) and doesn't take account of leap years (if that matters).

