## Sort week wise date in string format

Question

I have data in a table with two columns week and amount as shown below

Week                      Amount
--------------------------------
1w - 1m - 2015             1000
4w - 8m - 2015             1000
2w - 2m - 2015             1000
4w - 6m - 2015             1000
3w - 3m - 2015             1000
3w - 10m - 2015            1000
1w - 10m - 2015            1000
3w - 12m - 2015            1000
1w - 6m - 2016             1000
2w - 6m - 2016             1000
3w - 6m - 2016             1000
4w - 6m - 2016             1000
3w - 8m - 2016             1000
2w - 9m - 2016             1000

How can I sort this table by week (as date)?

My desired output is:

Week                    Amount
--------------------------------
1w - 1m - 2015             1000
2w - 2m - 2015             1000
3w - 3m - 2015             1000
4w - 6m - 2015             1000
4w - 8m - 2015             1000
1w - 10m - 2015            1000
3w - 10m - 2015            1000
3w - 12m - 2015            1000
1w - 6m - 2016             1000
2w - 6m - 2016             1000
3w - 6m - 2016             1000
4w - 6m - 2016             1000
3w - 8m - 2016             1000
2w - 9m - 2016             1000

Show source

## Answers to Sort week wise date in string format ( 2 )

1. Select *
From  YourTable
Order by right([Week],4)
,cast(substring([Week],charIndex('m',[Week])-2,2) as int)
,left([Week],1)

Returns

week            Amount
1w - 1m - 2015  1000
2w - 2m - 2015  1000
3w - 3m - 2015  1000
4w - 6m - 2015  1000
4w - 8m - 2015  1000
1w - 10m - 2015 1000
3w - 10m - 2015 1000
3w - 12m - 2015 1000
1w - 6m - 2016  1000
2w - 6m - 2016  1000
3w - 6m - 2016  1000
4w - 6m - 2016  1000
3w - 8m - 2016  1000
2w - 9m - 2016  1000

2. Bulding up on accepted answer, if table structure can be changes (add some columns), it can be made to store data in much more friendly way, by using persisted computed columns. This costs some storage and some extra computation effort on data insertion, but it will make most future selection faster, since string parsing is no longer required. Also, each column has the appropriate type, so it is harder to get invalid data.

Setup:

create table WeekData
(
WeekStr VARCHAR(16) NOT NULL,
Amount INT,
YearVal AS CAST (Right(WeekStr,4) AS SMALLINT) PERSISTED,
MonthVal AS cast(substring(WeekStr,charIndex('m', WeekStr)-2,2) as SMALLINT),
WeekVal AS CAST(left(WeekStr,1) AS TINYINT)
)

insert into WeekData (WeekStr, Amount)
VALUES ('1w - 1m - 2015', 1000),
('4w - 8m - 2015', 1000),
('2w - 2m - 2015', 1000),
('4w - 6m - 2015', 1000),
('3w - 3m - 2015', 1000),
('3w - 10m - 2015', 1000),
('1w - 10m - 2015', 1000),
('3w - 12m - 2015', 1000),
('1w - 6m - 2016', 1000),
('2w - 6m - 2016', 1000),
('3w - 6m - 2016', 1000),
('4w - 6m - 2016', 1000),
('3w - 8m - 2016', 1000),
('2w - 9m - 2016', 1000)
GO

Select

select WeekStr, Amount
from WeekData
order by YearVal, MonthVal, WeekVal