Is there a collation to sort the following strings in the following order 1,2,3,6,10,10A,10B,11? I have a database with a VARCHAR column that contains integers of varying length. I want to sort them so 10 comes after 9, not 1, and 70A comes after 70. I was able do this with PATINDEX(), a CTE, and CASE statements in the WHERE clause. 

However, I was wondering if there was a collation where this would be unecessary.
No. Collation is about alphabetical sorting, depending on code page, accent, case, width, kana. Numbers characters (0-9) have none of there properties.

So 9 is always after 10B in any sort. 

You have to split it up as you noted or sort like this:

ORDER BY
    RIGHT('                              ' + MyColumn, 30)


The length in the right determines how many spaces you have.

You could of course:


have 2 columns to make this unnecessary (and far quicker) and have a computed column to combine them
insist on leading zeros
right justify in a char (a stored version of my RIGHT above)


The latter 2 suggestions are like my RIGHT above and slightly different. Quicker to sort (no processing of the colukmn needed) but more storage required