Collation Sequence Case Sensitivity

Is there a way to query SQL Server through one of the system tables/views or one of the Information Schema views to determine if a given collation sequence is case sensitive or not?

Obviously a person can tell by looking at the collation sequence name and see if it is case sensitive or not, but how can you write a query which clearly and is guaranteed to display whether or not a collation sequence is case sensitive?

asked Jul 21 '11 at 06:16 AM in Default

guumbo
11 ● 1 ● 1 ● 1

(comments are locked)

1 answer:

If you do a select

select * from sys.fn_helpcollations()

In the description column you have written whether particular collation is Case Sensitive or Case Insensitive.

Also general rule is the the CS in the collation name means Case Sensitive and CI means Case Insensitive.

If you take a look in the sys.databases and sys.columns system views, you will see collations used for that db/column and by the names and eventually description returned by sys.fn_helpcollations() you can determine whether the collation is Case Sensitive or Insensitive.

answered Jul 21 '11 at 06:25 AM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

I was looking for was something a little more definitive. But this is not definitive as it is parsing natural english to determine the results. From the query you provided I use the following to get what I need: select name , case when charindex('case-sensitive',description)>0 then 1 else 0 end [Case Sensitive] from sys.fn_helpcollations() name Case Sensitive Albanian_BIN 0 Albanian_BIN2 0 Albanian_CI_AI 0 Albanian_CI_AI_WS 0 Albanian_CI_AI_KS 0 Albanian_CI_AS 0 Albanian_CI_AS_WS 0 Albanian_CI_AS_KS 0 Albanian_CS_AI 1 Albanian_CS_AI_WS 1 Albanian_CS_AI_KS 1

Jul 21 '11 at 07:13 AM guumbo

Sorry the table output didn't format properly. Seeing as my Karma is not > 60 the following is all I could do :(

name,Case Sensitive

Albanian_BIN,0

Albanian_BIN2,0

Albanian_CI_AI,0

Albanian_CI_AI_WS,0

Albanian_CI_AI_KS,0

Albanian_CI_AI_KS_WS,0

Albanian_CI_AS,0

Albanian_CI_AS_WS,0

Albanian_CI_AS_KS,0

Albanian_CI_AS_KS_WS,0

Albanian_CS_AI,1

Albanian_CS_AI_WS,1

Albanian_CS_AI_KS,1

Albanian_CS_AI_KS_WS,1

Jul 21 '11 at 07:26 AM guumbo

Binary Sorts are case sensitive. MSDN has a collation article that explains the collation naming scheme - http://msdn.microsoft.com/en-us/library/ms143515.aspx

looking at the _CI _CS _BIN portions of the collation name IS definitive, and is how the collation article determined the case sensitivity of a collation.

If you prefer to NOT parse the natural english, you must look for the _CI _CS and _BIN portions of the collation name.

Here is your natural english query with an update for case-sensitive binary

select name , case when charindex('case-sensitive',description)>0 OR charindex('binary',description)>0 then 1 else 0 end [Case Sensitive] from sys.fn_helpcollations()

Here is your query modified to not look at the natural english (same results)

select name , case when charindex('_CS',name)>0 OR charindex('_BIN',name)>0 then 1 else 0 end [Case Sensitive] from sys.fn_helpcollations()

Jul 21 '11 at 08:31 AM KenJ

(comments are locked)