'SQL sorting by a column that is a String

I am trying to write a query that will sort a column by the number that it is. However, the column is a string, so instead of sorting like 1, 2, 3, it will sort 1, 10, 11, 12, etc. Is there a way to change a column from a string to a number, or even a way to change how it orders the Strings. Here is my code and its output:

SELECT DISTINCT draft_number
    FROM NBA_data.dbo.all_seasons
        WHERE draft_year = '2016'
        ORDER BY draft_number;

Output:

draft_number
1
10
11
18
19
2
20
21
sqldatabasestringssmssqldatatypes


Solution 1:[1]

Just cast draft_number to datatype INT in Order By clause.

ORDER BY CAST(draft_number As INT);

Solution 2:[2]

Use ALTER TABLE to change the column type from string to number. Thus you don't have the hassle with every query on that column. Always use the appropriate data type for your data.

ALTER TABLE NBA_data.all_seasons ALTER COLUMN draft_number INT;

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=23324061a0c49d61a0172d46ad74bb77

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mudassir Hasan
Solution 2 Thorsten Kettner

Related Questions