Basically, you've stored it wrong: you should consider changing it to three fields and a computed column to "regenerate" the full name. Why? Because SQL is not good at string handling and you need to break up the date, convert part of it to an integer, and then use that as the sort value - or you will never get a "proper" sort.
Alternatively, change the names so they are all "zero padded":
CBSFBE20151202000017_000_0001.tif
CBSFBE20151202000017_000_0010.tif
CBSFBE20151202000017_000_0100.tif
CBSFBE20151202000017_000_0101.tif
CBSFBE20151202000017_000_0102.tif
But that generally impacts a lot of systems and is more trouble than it's worth.
YOu can do what you want, but ... you need to:
1) Break out the file name to remove everything after teh '.' - that's CHARINDEX and LEFT
2) Extract the part after the last '_' character - that's more complicated:
Is there a LastIndexOf in SQL Server? - Stack Overflow[
^] will show you how.
3) Convert the bit you have left - a string based number - to an integer, and use that as your ORDER BY clause.
Complicated? Yes. Nasty? Yes. Have to do it every time you want to do this? Yes.
See what I mean about "you've stored it wrong"?
Submit an article or tip