Order By with duration strings having time units I have a SQLite database with a table "Rate" who has a column "timeToMaturity". This column contains strings such as :


7Months
26Days
3Years


I'd like to order a query results by timeToMaturity (i.e. 26Days &lt; 7Months &lt; 3Years). However, because they are strings, I have no idea how I should proceed.

Thank you for your help
If (and it's a big if) the format remains constant with a number and a unit, you should be able to create a case statement that will get the total of the smallest unit. In your example, let's use days as the smallest unit:

CASE
    WHEN timeToMaturity LIKE '%Days' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 1
    WHEN timeToMaturity LIKE '%Weeks' THEN cast(rtrim(InterestRate.timeToMat,'abcdefghijklmnopqrstuvwxyz')as INT) * 7
    WHEN timeToMaturity LIKE '%Months' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 30
    WHEN timeToMaturity LIKE '%Years' THEN cast(rtrim(timeToMaturity,'abcdefghijklmnopqrstuvwxyz')as INT) * 365
        ELSE 0
    END AS TotalDaysToMaturity


Now, I realize that you may need to do something different when it comes to months as all months are not equal, but it may fit your needs. Perhaps someone would come by to figure that out dynamically.