SQL Server Check Case-Sensitivity

SQL Server check case-sensitivity?

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

How to do a case sensitive search in WHERE clause (I'm using SQL Server)?

Can be done via changing the Collation. By default it is case insensitive.

Excerpt from the link:

SELECT 1
FROM dbo.Customers
WHERE   CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS
    AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS

Or, change the columns to be case sensitive.

How can I write a case sensitive WHERE CLAUSE in SQL Server?

Normally SQL Server is not case sensitive. So 'ABC'='abc' is true in a where clause.

To make a where clause case sensitive, you can use COLLATE. Please try the below query instead:

WHERE
        [Address] = UPPER([Address]) COLLATE SQL_Latin1_General_CP1_CS_AS
        OR
        [Address] = LOWER([Address]) COLLATE SQL_Latin1_General_CP1_CS_AS

Case sensitive search query with IN clause - SQL Server

Just like you did your 2 examples, use COLLATE:

SELECT *
FROM table1
WHERE flag COLLATE sql_latin1_general_cp1_cs_as IN ('Yes', 'No');

Is the LIKE operator case-sensitive with SQL Server?

It is not the operator that is case sensitive, it is the column itself.

When a SQL Server installation is performed a default collation is chosen to the instance. Unless explicitly mentioned otherwise (check the collate clause bellow) when a new database is created it inherits the collation from the instance and when a new column is created it inherits the collation from the database it belongs.

A collation like sql_latin1_general_cp1_ci_as dictates how the content of the column should be treated. CI stands for case insensitive and AS stands for accent sensitive.

A complete list of collations is available at https://msdn.microsoft.com/en-us/library/ms144250(v=sql.105).aspx

(a) To check a instance collation

select serverproperty('collation')

(b) To check a database collation

select databasepropertyex('databasename', 'collation') sqlcollation

(c) To create a database using a different collation

create database exampledatabase
collate sql_latin1_general_cp1_cs_as 

(d) To create a column using a different collation

create table exampletable (
    examplecolumn varchar(10) collate sql_latin1_general_cp1_ci_as null
)

(e) To modify a column collation

alter table exampletable
alter column examplecolumn varchar(10) collate sql_latin1_general_cp1_ci_as null

It is possible to change a instance and database collations but it does not affect previously created objects.

It is also possible to change a column collation on the fly for string comparison, but this is highly unrecommended in a production environment because it is extremely costly.

select
  column1 collate sql_latin1_general_cp1_ci_as as column1
from table1

SQL- Ignore case while searching for a string

Use something like this -

SELECT DISTINCT COL_NAME FROM myTable WHERE UPPER(COL_NAME) LIKE UPPER('%PriceOrder%')

or

SELECT DISTINCT COL_NAME FROM myTable WHERE LOWER(COL_NAME) LIKE LOWER('%PriceOrder%')

How to determine if an instance of SQL Server is case sensitive or not

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

Returns "SQL_Latin1_General_CP1_CI_AS", the CI is what indicates case insensitivity

SQL Case Sensitive String Compare

Select * from a_table where attribute = 'k' COLLATE Latin1_General_CS_AS 

Did the trick.

SQL Server case insensitive queries

Is there a way to set a global directive so that it would effect every query?

No.

Collation is not a session property that applies to queries, and it cannot be changed dynamically.

The other problem with this request is that case-sensitivity is not an option that can be enabled or disabled by itself: it is a property of a collation, just like accent-sensitivity, width-sensitivity, what order the particular alphabet is arranged in, etc. A query can compare multiple fields, each with a different collation. So even if you could set a collation that would be in effect for the session, that would potentially force columns of other collations to convert collations on the fly when not even being requested to be case-insensitive. A global session setting would also affect sorting (i.e. TOP(n), ORDER BY, etc) and not just comparisons.

Since the issue is that a user wants to determine per execution whether or not to ignore part of the collation, there are a few options, but all will incur some performance penalty:

1. Construct the query (or queries) in Dynamic SQL:

   DECLARE @SQL NVARCHAR(MAX),
           @Collation NVARCHAR(50);
   
   SET @Collation = '';
   IF (@CaseInsensitive = 1)
   BEGIN
     SET @Collation = N'COLLATE Latin1_general_CI_AI';
   END;
   
   SET @SQL = N'SELECT *
     FROM Venue
     WHERE Name ' + @Collation + N' LIKE ''%' + @SearchParam
                  + N'%'' ' + @Collation;
   
   EXEC(@SQL);
   

2. Translate each character into upper-case and lower-case pairs in single-character ranges. This can be done in the app layer for the parameter value being searched on:

   • For default case-sensitive (don't do anything):
     
     @SearchParam = 'This'
   • For case-insensitive:
     
     @SearchParam = '[tT][hH][iI][sS]'

3. Force everything to the same case. Assume that the option to do case-insensitive is an additional parameter that is passed in:

   SELECT *
   FROM Venue
   WHERE CASE @CaseInsensitive
           WHEN 1 THEN LOWER(Name)
           ELSE Name
         END
            LIKE 
         CASE @CaseInsensitive
           WHEN 1 THEN '%' + LOWER(@SearchParam) + '%'
           ELSE '%' + @SearchParam + '%'
         END;
   

   Or, do the LOWER() prior to the query:

   IF (@CaseInsensitive = 1)
   BEGIN
     SET @SearchParam = LOWER(@SearchParam);
   END;
   
   SELECT *
   FROM Venue
   WHERE CASE @CaseInsensitive
           WHEN 1 THEN LOWER(Name)
           ELSE Name
         END
            LIKE '%' + @SearchParam + '%';
   

---

Related Topics