October 31, 2006 at 5:23 pm
Hi All,
I have a stored procedure that accepts a sort variable and then applies the relevant sort using a case statement. It works fine except for one anomaly. If I have a list of names like; Albert, Stan, Michael, Fred, de masi; when the list is sorted ascending, demasi comes out first and when it is sorted descending it comes out last. I assume this is because it places lower case characters before upper case characters? If so, how do I sort so that it is not case sensitive?
Cheers
ab
October 31, 2006 at 7:03 pm
Found it. For anyone who stumbles over this post the aswer it to use 'COLLATE Latin1_General_CS_AS' in the order by query. For Example:
ORDER BY CASE WHEN @varSort = 1 THEN tblHeaders.fldID End
,CASE WHEN @varSort = 2 THEN tblHeaders.fldID End desc
,CASE WHEN @varSort = 3 THEN PWIN171.dbo.FE.FE_NAME End
,CASE WHEN @varSort = 4 THEN PWIN171.dbo.FE.FE_NAME End desc
,CASE WHEN @varSort = 5 THEN CONVERT(int,tblHeaders.fldJobNumber) End
,CASE WHEN @varSort = 6 THEN CONVERT(int,tblHeaders.fldJobNumber) End desc
,CASE WHEN @varSort = 7 THEN PWIN171.dbo.RM.RM_NAME COLLATE Latin1_General_CS_AS End
,CASE WHEN @varSort = 8 THEN PWIN171.dbo.RM.RM_NAME COLLATE Latin1_General_CS_AS End desc
,CASE WHEN @varSort = 9 THEN CC_CauseCode.fldDescription End
,CASE WHEN @varSort = 10 THEN CC_CauseCode.fldDescription End desc
Cheers
ab
November 3, 2006 at 8:37 am
Actually it is easier to get all the fields to upper or lower case.
UPPER ( character_expression )
Returns a character expression with lowercase character data converted to uppercase.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply