x

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?
more ▼

asked Jul 21, 2011 at 06:16 AM in Default

guumbo gravatar image

guumbo
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.
more ▼

answered Jul 21, 2011 at 06:25 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

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, 2011 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, 2011 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, 2011 at 08:31 AM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x22
x3

asked: Jul 21, 2011 at 06:16 AM

Seen: 1173 times

Last Updated: Jul 21, 2011 at 06:16 AM