How to sort string in sql server

How to sort this data in sql server as Pre-OP 1, Pre-Op 2 l开发者_高级运维ike wise

Pre-OP 1
Pre-OP 10
Pre-OP 11
Pre-OP 12
Pre-OP 13
Pre-OP 14
Pre-OP 15
Pre-OP 16
Pre-OP 17
Pre-OP 18
Pre-OP 19
Pre-OP 2
Pre-OP 20
Pre-OP 21
Pre-OP 22
Pre-OP 23
Pre-OP 24
Pre-OP 25
Pre-OP 26
Pre-OP 27
Pre-OP 28
Pre-OP 29
Pre-OP 3
Pre-OP 30
Pre-OP 4
Pre-OP 5
Pre-OP 6
Pre-OP 7
Pre-OP 8
Pre-OP 9

If data have a space between number and text, you can use the following:

order by cast(right(col,CHARINDEX(' ',reverse(col))) as int)

I got an answer after doing a little bit of work so would like to share with you all, what are the flaws in this query. Please let me know

select room from m_room order by len(room), room

ORDER BY CAST(REPLACE(str, 'Pre-OP ', '') AS INT)

Try this:

Order BY 
    CASE WHEN isnumeric(emp_id) = '1' THEN cast (emp_id AS int) 
         WHEN isnumeric(substring(emp_id, 3, 7))=1 THEN cast (substring(emp_id, 3, 7) AS int) 
         ELSE '99999999'
    END 

Do the sorting on the substring. It will be like Select * from Table1 order by Cast(SUBSTRING(ColumnName, 7) as INT) ASC.