How to sort by number in SQL Server?

Question

I have a table with a column stored as string, but it is really a number like this:

17 - Doe
2 - Mike
3 - James

I need to sort them and create a output like this:

2 - Mike
3 - James
17 - Doe

How to write the SQL? Thanks in advance!

Answer 1

try this:

DECLARE @Yourtable table (data varchar(50))
insert into @Yourtable values ('17 - Doe')
insert into @Yourtable values ('2 - Mike')
insert into @Yourtable values ('3 - James')

SELECT * FROM @Yourtable order by CONVERT(int,left(data, charindex('-', data)-1))

You shouldn't store your data this way, add a new int column to this table and run this to fix your table:

DECLARE @Yourtable table (data varchar(50), newINT int)
insert into @Yourtable values ('17 - Doe',null)
insert into @Yourtable values ('2 - Mike',null)
insert into @Yourtable values ('3 - James',null)

UPDATE @Yourtable
    SET newINT=CONVERT(int,left(data, charindex('-', data)-1))
        ,data=RIGHT(data, LEN(data)-charindex('-', data)-1)

you can add an index to the new int column if you need to join or select by it. Now you can do a regular ORDER BY on it.