CODEWITHSUNDEEP
sqlsql-server
Sako73
Sako73

Reputation: 10147

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

Upvotes: 0

Views: 282

Answers (4)

Ben Thul
Ben Thul

Reputation: 32737

My approach would be to write a function that parses your duration string and normalizes it in the smallest possible units that can be expressed (I'm assuming that it's days, but if you can have hours or some smaller unit expressed, use that). Then, have a computed column that is the value of that function called against your column. At that point, it's just a matter of comparing the numbers. Of course, the devil's in the details... I don't relish trying to parse text from T-SQL though.

Upvotes: 0

Ray
Ray

Reputation: 46605

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).

Upvotes: 2

ibram
ibram

Reputation: 4589

Replace your characters with string numbers and convert to integer before sorting, Like:

convert(int, replace(replace(replace(myField,'d',''),'w','0')'y','00'))

So your 3d is 3, 2w is 20 and 4y is 400.

after thinking about a bit, it's gonna better to calculate the real values through a case expression. e.g. to get 14 for 2w which is less than 13d.

Upvotes: 1

CristiC
CristiC

Reputation: 22708

You may have different periods over time. So i would put this periods in a new table with ID, time period and sort order (or no. of days). In the initial table replace the field with time_period_id and make a join in your query and use it in SORT BY.

Upvotes: 2

Related Questions