Sql-server – SQL Case Sensitive Query

casesql servert-sql

I have two databases which return different results based on the case of the query.

On the first database I run the query 

FROM [Temp].[Team].[CLASS]
  WHERE [TABLE_NAME] like '%Link%'

and on the other database I run this query

FROM [Temp].[Team].[CLASS]
  WHERE [TABLE_NAME] like '%LINK%'

The first query returns nothing but the second query returns what I'd expect it to.

Checking the collation between the databases, I find they are both set to SQL_Latin1_General_CP1_CI_AS so I wouldn't expect them to behave differently.

What other settings do I need to alter to give me case insensitive queries without changing my queries?

Best Answer

You need to check that the collation of the servers and columns match (collation isn't only determined at the database level).

To check the servers:

SELECT SERVERPROPERTY('COLLATION');

To check the columns (a much, much, much more likely culprit for this symptom IMHO):

SELECT collation_name
  FROM [Temp].sys.columns AS c
   INNER JOIN [Temp].sys.objects AS o ON c.[object_id] = o.[object_id]
   INNER JOIN [Temp].sys.schemas AS s ON o.[schema_id] = s.[schema_id]
WHERE UPPER(c.name = N'TABLE_NAME')
  AND UPPER(o.name = N'CLASS')
  AND UPPER(s.name = N'TEAM');

If these don't match, then your choices are:

  1. Make them match. E.g. on the server where the collation is case sensitive, you can make it not case sensitive using:

    ALTER TABLE [Team].[CLASS] 
  ALTER COLUMN [TABLE_NAME] VARCHAR(32) -- guessing
  COLLATE SQL_Latin1_General_CP1_CI_AS 
  NOT NULL; -- also guessing

  2. Use a case-insensitive query, e.g. 

    WHERE UPPER([TABLE_NAME]) LIKE '%LINK%';

-- or

WHERE [TABLE_NAME] COLLATE SQL_Latin1_General_CP1_CI_AS
  LIKE '%LINK%';

Also be sure to use an N prefix if the column is NVARCHAR.

Related Question