3

I need to determine programmatically (C#) if the connected database (could be either Oracle 11g or Sql Server 2008 onwards) has been set to be case-sensitive, so I know when I compare two strings (one retrieved from the database, one entered by a user) whether to make a case-sensitive comparison or not.

Is this possible, and if so, how do I do that?

TIA

haughtonomous
  • 4,602
  • 11
  • 34
  • 52

1 Answers1

1

You could just use a case with a select

SELECT CASE WHEN 'A' = 'a' THEN 'Insensitive' ELSE 'Sensitive' END

The above is VERY general for the database session as a whole. To be more specific, you have to get more into the database configurations for each DBMS.

The following checks specific for Oracle's configuration of case sensitivity: 

SELECT CASE WHEN COUNT(*) = 2 THEN 'Insensitive' ELSE 'Sensitive' END
FROM NLS_SESSION_PARAMETERS
WHERE (PARAMETER = 'NLS_COMP' AND VALUE = 'LINGUISTIC')
    OR (PARAMETER = 'NLS_SORT' AND VALUE = 'BINARY_CI')

The following checks for specific SQL Server collations at various levels:

-- Server level
SELECT CASE WHEN SERVERPROPERTY('COLLATION') LIKE '%_CI_%' THEN 'Insensitive' ELSE 'Sensitive' END

-- Database level
SELECT CASE WHEN DATABASEPROPERTYEX('AdventureWorks', 'Collation') LIKE '%_CI_%' THEN 'Insensitive' ELSE 'Sensitive' END

-- Column level
SELECT CASE WHEN COLLATION_NAME LIKE '%_CI_%' THEN 'Insensitive' ELSE 'Sensitive' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
    AND COLUMN_NAME = @column_name

This is not exhaustive for checking case insentive collations, but based on SELECT name, description FROM ::fn_helpcollations(), it seems pretty good.

Reference for SQL Server collations (as noted from comment on the OP)

Community
  • 1
  • 1
Jason W
  • 13,026
  • 3
  • 31
  • 62
  • Neat. Now for an Oracle version (Gulp) – haughtonomous Jun 30 '15 at 11:55
  • That should work for either since `CASE` is supported by both. I believe Oracle is case sensitive by default. If you're using Entity Framework, then just use the database property of the context to run the SQL query directly. – Jason W Jun 30 '15 at 11:58
  • Updated answer to be more specific on each dbms. You have to be cautious since each DBMS handles case sensitivity very differently, and the configurations can be different based on the server, database, or even column you're working on. – Jason W Jun 30 '15 at 12:21
  • Oracle requires the From Dual clause, plus a semi-colon on the end, but otherwise its the same. – haughtonomous Jun 30 '15 at 14:15