Ordering based on number value in a string from SQL

Question

I have these CB1-15 values however I want to order them by... CB1-15 but currently it goes CB1, CB10, CB11 etc.

This is how my table looks...

CB    Description   File Path

CB1   New Product   new.png
CB10            
CB11            
CB12            
CB13            
CB14            
CB15            
CB2   Best Seller   best.png
CB3         
CB4         
CB5         
CB6         
CB7         
CB8         
CB9         

Thanks for helping :)

Answer 1

Use the below script.

  SELECT * 
  FROM  YourTable
  ORDER BY CAST(Replace(YourColumn,'CB','')as INT)

Answer 2

It's a little unclear if that data is in multiple columns or a single string. Here's one method that should work in both cases:

select *
from yourtable
order by cast((substring(yourfield, 3, 2)) as int)

• SQL Fiddle Demo

The key is using cast to order by an int instead of the string.