Yesterday was a very good learning day for me. One of my colleagues stuck in a very interesting issue.
He was fetching records from database with an ORDER BY clause on a VARCHAR field.
Couple of records of his table containing “-“hyphen symbol in the field on which he applied Sorting.
So after facing that problem I am sharing how to use collation in ORDER BY CLAUSE with your select statement.
Let’s consider below example
DECLARE @tblSubjects TABLE(
INSERT INTO @tblSubjects VALUES (‘HINDIBALBHARTI’)
INSERT INTO @tblSubjects VALUES (‘HINDI-BALBHARTI’)
SELECT Subjects AS Default_Collation_Sort_Order FROM @tblSubjects ORDER BY Subjects ;
SELECT Subjects AS Explicit_Changed_Collation_Sort_Order FROM @tblSubjects ORDER BY Subjects COLLATE Latin1_General_CI_AI ;
When you run above statement you will get different order in result set.
So if you found this type of ORDER BY issue than you can explicitly use COLLATION