SQL Server Sort varchar columns depend on numbers inside like int and decimal-CodePudding

Hello looking solution to sort sizes inside my column. Exmaple :

-- CREATE TEMP TABLE 
Create Table #MyTempTable (
    size varchar(20)
);

-- insert sample data to TEMP TABLE 
insert into #MyTempTable
values 
('10.5W'),
('10W'),
('11.5W'),
('11W'),
('12W'),
('5.5W'),
('5W'),
('6.5W'),
('6W'),
('7.5W'),
('7W'),
('8.5W'),
('8W'),
('9.5W'),
('9W'),
('4')


select 'BEFORE',* from #MyTempTable


SELECT 'AFTER',size 
FROM #MyTempTable 
ORDER BY LEN(size)

When i order by LEN there is no good sorting like this :

AFTER   5W
AFTER   6W
AFTER   7W
AFTER   8W
AFTER   9W
AFTER   10W
AFTER   11W
AFTER   12W
AFTER   5.5W
AFTER   7.5W
AFTER   6.5W
AFTER   9.5W
AFTER   8.5W
AFTER   10.5W
AFTER   11.5W

All im' looking for is to sort in proper order. like this :

5W
5.5W
6W
6.5W
7W
7.5W
8W
8.5W
9W
9.5W
10W
10.5W
11W
11.5W
12W

I seearched a lot of stackoverflow and can't find solution for that because there is not only int and also decimal numbers. So don't know how to get it

CodePudding user response：

In order by clause first remove W from then cast as NUMERIC data type. Now you can expect sorting like a number.

SELECT *
FROM #MyTempTable AS mtt
ORDER BY CAST(LEFT(mtt.size, LEN(mtt.size) - 1) AS DECIMAL) ASC;

CodePudding user response：

Assuming each value would always end in just one unit, you may sort on the numeric portion cast to a decimal:

SELECT size 
FROM #MyTempTable 
ORDER BY CAST(
    CASE WHEN size LIKE '%[A-Z]'
         THEN LEFT(size, LEN(size) - 1)
         ELSE size END AS DECIMAL(10, 2)
);