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.

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

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

StackExchange Database Administrators Q#3828, answer score: 9