How to Sort a Varchar Column in SQL Server That Contains Numbers

How do I sort a VARCHAR column in SQL server that contains numbers?

One possible solution is to pad the numeric values with a character in front so that all are of the same string length.

Here is an example using that approach:

select MyColumn
from MyTable
order by 
    case IsNumeric(MyColumn) 
        when 1 then Replicate('0', 100 - Len(MyColumn)) + MyColumn
        else MyColumn
    end

The 100 should be replaced with the actual length of that column.

Sort a VARCHAR column in SQL Server that contains numbers?

This should work

select name_subagent
from Subagent
order by CAST(LEFT(name_subagent, PATINDEX('%[^0-9]%', name_subagent + 'a') - 1) as int)

Sorting a VARCHAR column with numbers and letters

You can use a case expression to first split the results into 2 groups and order by that, then your existing condition:

order by case when Total + 0 > 0 then 0 else 1 end, Total + 0;

Order By a Varchar column containing numbers with a dash (1060006-19)

I think the efficient way would be using splitted columns in Order By

Schema:

CREATE TABLE #TAB (VARCHARCOLUMN VARCHAR(20))
INSERT INTO #TAB 
SELECT '1060006-1' 
UNION ALL 
SELECT  '1060006-10' 
UNION ALL 
SELECT  '1060006-11' 
UNION ALL 
SELECT  '1060006-12' 
UNION ALL  
SELECT '1060006-13' 
UNION ALL 
SELECT  '1060006-15' 
UNION ALL 
SELECT  '1060006-16' 
UNION ALL 
SELECT  '1060006-17' 
UNION ALL 
SELECT '1060006-18' 
UNION ALL 
SELECT  '1060006-19' 
UNION ALL 
SELECT  '1060006-20' 
UNION ALL 
SELECT  '1060006-2'

Now split the varchar column in parts by - and get the order

SELECT VARCHARCOLUMN
,CAST(SUBSTRING(VARCHARCOLUMN,1,CHARINDEX('-', VARCHARCOLUMN)-1) AS BIGINT) AS PART1
,CAST(SUBSTRING(VARCHARCOLUMN,CHARINDEX('-', VARCHARCOLUMN)+1,LEN(VARCHARCOLUMN)) AS INT) AS PART2
FROM #TAB
ORDER BY PART1, PART2

This will return

+---------------+---------+-------+
| VARCHARCOLUMN |  PART1  | PART2 |
+---------------+---------+-------+
| 1060006-1     | 1060006 |     1 |
| 1060006-2     | 1060006 |     2 |
| 1060006-10    | 1060006 |    10 |
| 1060006-11    | 1060006 |    11 |
| 1060006-12    | 1060006 |    12 |
| 1060006-13    | 1060006 |    13 |
| 1060006-15    | 1060006 |    15 |
| 1060006-16    | 1060006 |    16 |
| 1060006-17    | 1060006 |    17 |
| 1060006-18    | 1060006 |    18 |
| 1060006-19    | 1060006 |    19 |
| 1060006-20    | 1060006 |    20 |
+---------------+---------+-------+

Sql Server query varchar data sort like int

Cast amount column into Numeric in ORDER BY clause while selecting:

SELECT * FROM MyTable
ORDER BY CAST(amount AS Numeric(10,0)) DESC

You can change the decimal point value according to your requirement to get more precise result:

SELECT * FROM MyTable
ORDER BY CAST(amount AS Numeric(10,2)) DESC
                                   ^

Result:

---