Case Insensitive SQL Server Queries

Question

I want to ignore the case when comparing a string in my queries using SQL Server. For now, I can do this using something like this:

SELECT *
FROM Venue
WHERE
   Name COLLATE Latin1_general_CI_AI Like '%cafe%' COLLATE Latin1_general_CI_AI

Is there a way to set a global directive so that it executes every request? Something like that:

SET COLLATE Latin1_general_CI_AI;
SELECT *
    FROM Venue
    WHERE
       Name Like '%this%';
SELECT *
    FROM Venue
    WHERE
       Name Like '%that%';
...

Thank!

Answer 1

Is there a way to set a global directive so that it executes every request?

No.

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

, , : , , -, , .. , . , , , , " ", . (.. TOP (n), ORDER BY ..), .

, , , , :

• ( ) 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);
  

• . :

  • ( ): @SearchParam = 'This'
  • :
    @SearchParam = '[tT] [hH] [iI] [sS]'

• . , - , :

  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;
  

  LOWER() :

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