Sql sort script based on one column that has odd or even value

Question

I need help devising a sql script that will sort a bay item, based on the column value. If the level column value is even, then the bay ascends, but if the level column is odd, then bay descends. I have 12 levels, and 54 bays per level. So, shortened example (disregard dots after level number)

Level  Bay
    1    5
    1    4
    1   54
    1   13
    2    8
    2    3
    2    4

Result

Level  Bay
    1    4
    1    5
    1   13
    1   54
    2    8
    2    4
    2    3

Here is the script as it gives a conversion failed error when converting the varchar level to an int.

SELECT cast (SUBSTRING(cm.description, CHARINDEX('Bay:', cm.description) + 4, CHARINDEX(' ', cm.description, CHARINDEX('Bay:', cm.description)) - CHARINDEX('Bay:', cm.description) - 4) AS INT)      AS Bay,
       cast (SUBSTRING(cm.description, CHARINDEX('Level:', cm.description) + 6, CHARINDEX(' ', cm.description, CHARINDEX('Level:', cm.description)) - CHARINDEX('Level:', cm.description) - 6)AS INT) AS level
FROM   [Test].[Test].[Stations] AS cm
WHERE  ( cm.StationTypeId = '1' )
ORDER  BY CASE 'level' % 2
            WHEN 0 THEN 'bay'
            WHEN 1 THEN '-bay'
          END; 

Here is what the description column looks like for each level:

Aisle:1 Bay:1 Level:1 Side:E

Answer 1

Assuming bay is actually a string, you can't use the other answer's trick of negating it.

Instead, use separate clauses.

you also cannot sort by a calcualtion on a field created in the SELECT. Instead put it into an APPLY subquery.

SELECT
  v.bay,
  v.level
FROM Test.Stations AS cm
CROSS APPLY (
    SELECT
      cast (SUBSTRING(cm.description, CHARINDEX('Bay:', cm.description) + 4, CHARINDEX(' ', cm.description, CHARINDEX('Bay:', cm.description)) - CHARINDEX('Bay:', cm.description) - 4) AS INT)      AS Bay,
      cast (SUBSTRING(cm.description, CHARINDEX('Level:', cm.description) + 6, CHARINDEX(' ', cm.description, CHARINDEX('Level:', cm.description)) - CHARINDEX('Level:', cm.description) - 6)AS INT) AS level
) AS v
WHERE  cm.StationTypeId = '1'
order by
    v.level,
    case when v.level % 2 = 1 then bay end asc,
    case when v.level % 2 = 0 then bay end desc;