Sort SQL by the number contained as a substring

Question

I have such data

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 order so that when sorting by it it will look like

 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 numbers should either be sorted in ascending or descending order (depending on whether the bottom or top one) and anything else without a number, just sort alphabetically and place between them.

I think that I have a group by the first characters of each subgroup, and then I convert the number and order. But this 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 sqlfiddle

Edit: Changed the data values ​​since they include 1, as well as 10, which will not be sorted correctly if they are varchars.

Answer 1

You can try the following:

 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 instruction:

 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 a SELECT base that you can use to update your table or to get results

Fixed issues with:

 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 

Answer 3

Slightly compressed version:

 SELECT Name FROM (SELECT Name, SUBSTRING(Name, PATINDEX('%[0-9]%', Name), LEN(Name) - PATINDEX('%[0-9]%', Name) - PATINDEX('%[0-9]%', REVERSE(Name)) + 2) n FROM @t) t ORDER BY 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, CASE WHEN Name LIKE 'lower%' OR Name LIKE 'min_low%' THEN CAST(n AS DECIMAL(10, 2)) END DESC, CASE WHEN Name LIKE 'upper%' OR Name LIKE 'min_up%' THEN CAST(n AS DECIMAL(10, 2)) END, Name)