In October of 2011, I shared an example of a peculiar set of sort requirements. Today, I am going to share another similar set of requirements. Based on prior experience, when I saw this request on a sort order, I was sure I could find a simpler solution. Today, I will share the requirements and solution with you.
Requirements
Given a set of characters, you must be able to sort according to the following:
- !
- “
- $
- ?
- @
- ^
- {
- >
- ASCII values
Setup
To demonstrate the requirements and solution, let’s create a temp table and populate it with some values like those in the requirements.
create table #Chars ( MyChar char(1) ) INSERT INTO #Chars (MyChar) values ('!'), ('"'), ('$'), ('?'), ('@'), ('^') , ('{'), ('>'), ('1'), ('2'), ('3'), ('4') , ('5'), ('6'), ('7'), ('8'), ('9'), ('A') , ('B'), ('C'), ('D')
Solution
SELECT * FROM #Chars ORDER BY MyChar COLLATE SQL_Latin1_General_Cp1251_CS_AS
Sometimes the simplest solution requires a lot of testing. When I came across the requirements, I thought it could be solved via a collation. The problem was that I did not know which collation. I had to test a few collations to find the collation that would create the correct result set. If you are interested in learning about other collations, you can read this article.
Update 2019-05-21: Please read the comments from Solomon.