SQL Server - Query on specifying Sort collation for a SELECT Query for a Multilingual App

Question

User-830595639 posted

Hi All,

I have a requirement as follows for a multilingual application, where the columns displayed on a search are picked up from configuration, and the data can be sorted based on configuration.

Question 1 - I am using a Select query like this:

"SELECT * FROM TABLE ORDER BY 1 COLLATE SQL_Latin1_General_CP1_CI_AS DESC, ORDER BY 4 COLLATE SQL_Latin1_General_CP1_CI_AS DESC"

Getting an error "Expression type int is invalid for COLLATE clause."

Question 2 - I would also need to support integer column based sorting like "ID"

But get the same error as mentioned for question 1

Are there any workaround to support both use-cases.

Or is this an SQL Server limitation that we have to live with even with the latest version of SQL Server 2016?

Would appreciate a quick response.

Thanks & Regards,

Nishant

Answer 1

User-595703101 posted

Hello Nishant,

Please note that COLLATION property can be applied only to character data types

So you cannot use COLLATE with int, bigint, etc data types only on varchar and nvarchar, etc character data types can be used with collation property

In fact you don't need collation for numeric values, 1,3,6 numbers are all same in every culture or language

Please check following Select

Use field names and do not use ORDER BY repeatedly

SELECT * 
FROM [user] 
ORDER BY 
	USERNAME  COLLATE SQL_Latin1_General_CP1_CI_AS DESC,
	userID  DESC

I hope it helps

Answer 2

User364663285 posted

Please try to remove 'Collate' option to the Order by clause.