How to sort string in sql server

Question

How to sort this data in sql server as Pre-OP 1, Pre-Op 2 like 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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

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