Sql Server Queries Case Sensitivity

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

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

SQL Case Sensitive Group By

Assign the COLLATE also on the GROUP BY clause

select accountCode COLLATE Latin1_General_CS_AS, count(OrderId)  
from <<TableName>>  
group by accountCode COLLATE Latin1_General_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');

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 + '%';
   

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%')

---

Related Topics