Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server check case-sensitivity?

How can I check to see if a database in SQL Server is case-sensitive? I have previously been running the query:

SELECT CASE WHEN 'A' = 'a' THEN 'NOT CASE SENSITIVE' ELSE 'CASE SENSITIVE' END

But I am looking for other ways as this has actually given me issues in the past.

Edit - A little more info: An existing product has many pre-written stored procedures. In a stored procedure @test != @TEST depending on the sensitivity of the server itself. So what I'm looking for is the best way to check the server for its sensitivity.

like image 948
Kyle Avatar asked Sep 11 '09 14:09

Kyle


People also ask

How can I tell if SQL Server case sensitive?

SQL Server is, by default case insensitive; however, it is possible to create a case sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine a database or database object is by checking its “COLLATION” property and look for “CI” or “CS” in the result.

What is case sensitivity in SQL?

SQL Case sensitivity is to use the query statements and the keywords tables and columns by specifying them in capital or small letters of alphabets. SQL keywords are by default set to case insensitive, which means that the keywords are allowed to be used in lower or upper case.

How do you match case insensitive in SQL?

Case insensitive SQL SELECT: Use upper or lower functions or this: select * from users where lower(first_name) = 'fred'; As you can see, the pattern is to make the field you're searching into uppercase or lowercase, and then make your search string also be uppercase or lowercase to match the SQL function you've used.

Is SQL case sensitive WHERE clause?

By default it is case insensitive.


6 Answers

Collation can be set at various levels:

  1. Server
  2. Database
  3. Column

So you could have a Case Sensitive Column in a Case Insensitive database. I have not yet come across a situation where a business case could be made for case sensitivity of a single column of data, but I suppose there could be.

Check Server Collation

SELECT SERVERPROPERTY('COLLATION')

Check Database Collation

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

Check Column Collation

select table_name, column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @table_name
like image 56
Raj More Avatar answered Oct 05 '22 13:10

Raj More


If you installed SQL Server with the default collation options, you might find that the following queries return the same results:

CREATE TABLE mytable 
( 
    mycolumn VARCHAR(10) 
) 
GO 

SET NOCOUNT ON 

INSERT mytable VALUES('Case') 
GO 

SELECT mycolumn FROM mytable WHERE mycolumn='Case' 
SELECT mycolumn FROM mytable WHERE mycolumn='caSE' 
SELECT mycolumn FROM mytable WHERE mycolumn='case' 

You can alter your query by forcing collation at the column level:

SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'caSE' 

SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'case' 

SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'Case' 

-- if myColumn has an index, you will likely benefit by adding 
-- AND myColumn = 'case' 

SELECT DATABASEPROPERTYEX('<database name>', 'Collation') 

As changing this setting can impact applications and SQL queries, I would isolate this test first. From SQL Server 2000, you can easily run an ALTER TABLE statement to change the sort order of a specific column, forcing it to be case sensitive. First, execute the following query to determine what you need to change it back to:

EXEC sp_help 'mytable' 

The second recordset should contain the following information, in a default scenario:

Column_Name Collation


mycolumn SQL_Latin1_General_CP1_CI_AS

Whatever the 'Collation' column returns, you now know what you need to change it back to after you make the following change, which will force case sensitivity:

ALTER TABLE mytable 
    ALTER COLUMN mycolumn VARCHAR(10) 
    COLLATE Latin1_General_CS_AS 
GO 



SELECT mycolumn FROM mytable WHERE mycolumn='Case' 
SELECT mycolumn FROM mytable WHERE mycolumn='caSE' 
SELECT mycolumn FROM mytable WHERE mycolumn='case' 

If this screws things up, you can change it back, simply by issuing a new ALTER TABLE statement (be sure to replace my COLLATE identifier with the one you found previously):

ALTER TABLE mytable 
    ALTER COLUMN mycolumn VARCHAR(10) 
    COLLATE SQL_Latin1_General_CP1_CI_AS 

If you are stuck with SQL Server 7.0, you can try this workaround, which might be a little more of a performance hit (you should only get a result for the FIRST match):

SELECT mycolumn FROM mytable WHERE 
    mycolumn = 'case' AND 
    CAST(mycolumn AS VARBINARY(10)) = CAST('Case' AS VARBINARY(10)) 

SELECT mycolumn FROM mytable WHERE 
    mycolumn = 'case' AND 
    CAST(mycolumn AS VARBINARY(10)) = CAST('caSE' AS VARBINARY(10)) 

SELECT mycolumn FROM mytable WHERE 
    mycolumn = 'case' AND 
    CAST(mycolumn AS VARBINARY(10)) = CAST('case' AS VARBINARY(10)) 

-- if myColumn has an index, you will likely benefit by adding 
-- AND myColumn = 'case' 
like image 44
Will Marcouiller Avatar answered Oct 05 '22 12:10

Will Marcouiller


SQL server determines case sensitivity by COLLATION.

COLLATION can be set at various levels.

  1. Server-level
  2. Database-level
  3. Column-level
  4. Expression-level

Here is the MSDN reference.

One can check the COLLATION at each level as mentioned in Raj More's answer.

Check Server Collation

SELECT SERVERPROPERTY('COLLATION')

Check Database Collation

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

Check Column Collation

select table_name, column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @table_name

Check Expression Collation

For expression level COLLATION you need to look at the expression. :)

It would be generally at the end of the expression as in below example.

SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;

Collation Description

For getting description of each COLLATION value try this.

SELECT * FROM fn_helpcollations()

And you should see something like this.

enter image description here

You can always put a WHERE clause to filter and see description only for your COLLATION.

You can find a list of collations here.

like image 35
Devraj Gadhavi Avatar answered Oct 05 '22 14:10

Devraj Gadhavi


You're interested in the collation. You could build something based on this snippet:

SELECT DATABASEPROPERTYEX('master', 'Collation');

Update
Based on your edit — If @test and @TEST can ever refer to two different variables, it's not SQL Server. If you see problems where the same variable is not equal to itself, check if that variable is NULL, because NULL = NULL returns `false.

like image 29
Joel Coehoorn Avatar answered Oct 05 '22 12:10

Joel Coehoorn


The best way to work with already created tables is that, Go to Sql Server Query Editor

Type: sp_help <tablename>

This will show table's structure , see the details for the desired field under COLLATE column.

then type in the query like :

SELECT myColumn FROM myTable  
WHERE myColumn COLLATE SQL_Latin1_General_CP1_CI_AS = 'Case'

It could be different character schema <SQL_Latin1_General_CP1_CI_AS>, so better to find out the exact schema that has been used against that column.

like image 34
DareDevil Avatar answered Oct 05 '22 14:10

DareDevil


How can I check to see if a database in SQL Server is case-sensitive?

You can use below query that returns your informed database is case sensitive or not or is in binary sort(with null result):

;WITH collations AS (
    SELECT 
        name,
        CASE 
            WHEN description like '%case-insensitive%' THEN 0 
            WHEN description like '%case-sensitive%' THEN 1 
        END isCaseSensitive
    FROM 
        sys.fn_helpcollations()
)
SELECT *
FROM collations
WHERE name = CONVERT(varchar, DATABASEPROPERTYEX('yourDatabaseName','collation'));

For more read this MSDN information ;).

like image 44
shA.t Avatar answered Oct 05 '22 13:10

shA.t