SQL sort by number contained as substring

Question

I have data like this

Name     SortOrder
-------------------
Lower 1      3
Lower 10      2
Lower 2      1
Lower 1%     6 
Lower 1.5%   5
Lower 3%     4
Average      7 
Upper 1      10
Upper 10      8
Upper 1%     12
Upper 1.5%   11
Average       10  /* sorted 
Average Poor  11  * alphabetically
Rich          12  * only */
Min_Low10     17
Min_Low20     18
Min_Low30     19
Min_Up10      20
Min_Up20      21
Min_Up30      22

I want to update the sort orders so that when I sort by it, it will be like this

Name     SortOrder
-------------------
Lower 3%     1
Lower 1.5%   2
Lower 1%     3 
Lower 10      4
Lower 2      5
Lower 1      6
Min_Low30     7
Min_Low20     8
Min_Low10     9
Average       10  /* sorted 
Average Poor  11  * alphabetically
Rich          12  * only */
Min_Up10      13
Min_Up20      14
Min_Up30      15
Upper 1      16
Upper 2      17
Upper 10      18
Upper 1%     19
Upper 1.5%   20

i.e. those subgroups with a numerics should either be sorted ascending or descending (depending if its lower or upper) and anything else without a numeric should be simply sorted alphabetically and put inbetween.

I think I have group by the first characters of each subgroup then convert the numeric and order by that. But it gives me an error message when I try to convert it: Error converting data type varchar to numeric.

SELECT * 
  ,RowNum =
  CASE 
      WHEN Name LIKE 'Lower %[%]' THEN 
          ROW_NUMBER() over (partition by SUBSTRING(Name,1,5) ORDER By CAST(SUBSTRING(Name,7,LEN(Name)-3) as decimal) DESC)
      WHEN Name LIKE 'Lower %' THEN 
          ROW_NUMBER() over (partition by SUBSTRING(Name,1,5) ORDER By SUBSTRING(Name,7,LEN(Name)) DESC)
      WHEN Name LIKE 'Upper %[%]' THEN 
          (ROW_NUMBER() over (partition by SUBSTRING(Name,1,5) ORDER By SUBSTRING(Name,7,LEN(Name)-3)))
      WHEN Name LIKE 'Upper %' THEN 
          (ROW_NUMBER() over (partition by SUBSTRING(Name,1,5) ORDER By SUBSTRING(Name,7,LEN(Name))))

      ELSE -1
  END
  FROM Table1 
  ORDER BY RowNum

here is the sqlfiddle

Edit: changed data values as they include 1 as well as 10 which would be sorted wrong if they are varchars.

Answer 1

You can try this:

select *
    , RowNum =
     ROW_NUMBER() over (order by 
        case 
            when Name LIKE 'Lower %[%]' then 1
            when Name LIKE 'Lower %' then 2
            when Name LIKE 'Min_Low%' then 3
            when Name LIKE 'Min_Up%' then 5
            when Name LIKE 'Upper %[%]' then 7
            when Name LIKE 'Upper %' then 6
            else 4
        end
        , convert(numeric(6,2), case 
            when Name LIKE 'Lower %[%]' then replace(replace(Name, 'Lower ',''),'%', '')
            when Name LIKE 'Lower %' then replace(Name, 'Lower ','')
            when Name LIKE 'Min_Low%' then replace(Name, 'Min_Low','')
        end) desc
        , convert(numeric(6,2), case 
            when Name LIKE 'Min_Up%' then replace(Name, 'Min_Up','')
            when Name LIKE 'Upper %[%]' then replace(replace(Name, 'Upper ',''),'%', '')
            when Name LIKE 'Upper %' then replace(Name, 'Upper ','')
        end)
        , Name
    ) 
from #Table1

UPDATE

Here is the update statement:

update #Table1 set SortOrder = t2.RowNum
from #Table1 t1
  join (
select *
    , RowNum =
     ROW_NUMBER() over (order by 
        case 
            when Name LIKE 'Lower %[%]' then 1
            when Name LIKE 'Lower %' then 2
            when Name LIKE 'Min_Low%' then 3
            when Name LIKE 'Min_Up%' then 5
            when Name LIKE 'Upper %[%]' then 7
            when Name LIKE 'Upper %' then 6
            else 4
        end
        , convert(numeric(6,2), case 
            when Name LIKE 'Lower %[%]' then replace(replace(Name, 'Lower ',''),'%', '')
            when Name LIKE 'Lower %' then replace(Name, 'Lower ','')
            when Name LIKE 'Min_Low%' then replace(Name, 'Min_Low','')
        end) desc
        , convert(numeric(6,2), case 
            when Name LIKE 'Min_Up%' then replace(Name, 'Min_Up','')
            when Name LIKE 'Upper %[%]' then replace(replace(Name, 'Upper ',''),'%', '')
            when Name LIKE 'Upper %' then replace(Name, 'Upper ','')
        end)
        , Name
    ) 
from #Table1) t2 on t1.Name=t2.Name

Answer 2

The following is the base SELECT you could use for update your table or directly to get results

Fixed the issues using:

SELECT Name, ROW_NUMBER() over (ORDER By RowNumber) SortOrder
FROM (
SELECT TOP 100 PERCENT Name, ROW_NUMBER() over (ORDER By RowName, Value DESC) RowNumber
FROM (SELECT TOP 100 PERCENT Name,RowName = CASE 
        WHEN Name LIKE 'Lower %[%]%' THEN 'LowerP'
        WHEN Name LIKE 'Lower %' THEN 'LowerS'
        WHEN Name LIKE 'Min_Low%' THEN 'LowerX'
        ELSE 'LowerZ' + Name
    END, Value = CASE 
        WHEN Name LIKE 'Lower %[%]%' THEN CONVERT(float, SUBSTRING(Name, 6, LEN(Name)-6))
        WHEN Name LIKE 'Lower %' THEN CONVERT(float, SUBSTRING(Name, 6, 10))
        WHEN Name LIKE 'Min_Low%' THEN CONVERT(float, SUBSTRING(Name, 8, 10))
        ELSE 100
        END
    FROM Sorting 
    WHERE Name NOT LIKE '%Up%'
    ORDER BY RowName, Value DESC) a
UNION
SELECT TOP 100 PERCENT Name, ROW_NUMBER() over (ORDER By RowName, Value ASC) + 1000 RowNumber 
FROM (
    SELECT TOP 100 PERCENT Name,RowName = CASE 
        WHEN Name LIKE 'Min_Up%' THEN 'UpperA'
        WHEN Name LIKE 'Upper %' AND NOT SUBSTRING(Name, LEN(Name), 1) = '%' THEN 'UpperB'
        WHEN SUBSTRING(Name, LEN(Name), 1) = '%' THEN 'UpperC'
        ELSE 'Middle'
    END, Value = CASE 
        WHEN Name LIKE 'Upper %[%]%' THEN CONVERT(float, SUBSTRING(Name, 6, LEN(Name)-6))
        WHEN Name LIKE 'Upper %' THEN CONVERT(float, SUBSTRING(Name, 6, 10))
        WHEN Name LIKE 'Min_Up%' THEN CONVERT(float, SUBSTRING(Name, 7, 10))
        ELSE 100
        END
FROM Sorting 
WHERE Name LIKE '%Up%'
ORDER BY RowName, Value ASC ) b
ORDER BY RowNumber ) c