Conditional COLLATE on SQL Server query

Here's a little gift for those of you trying to have conditional COLLATE while searching for some text in a SQL Server table.

This is useful when you want to have an easy control on case insensitiveness. That is, use a flag to set it ON or OFF.

The difficulty of having conditional COLLATE is that SQL Server applies a COLLATE statement to the entire clause. For example, if there's one COLLATE within a CASE, it will be applied to the entire CASE and trying to use different COLLATE statements will raise a conflict error.

It's also not possible to specify the COLLATE value as a variable unless you use dynamic SQL.

So here's my answer to this problem:

declare @ignoreCase BIT = 1

declare @pattern VARCHAR(max) = '%featureconfig%'

SELECT

       Name, 
       SUBSTRING(

              Script,

              CASE WHEN @ignoreCase = 1 THEN PATINDEX(@pattern, Script COLLATE Latin1_General_CI_AI) ELSE 0 END

              +

              CASE WHEN @ignoreCase = 0 THEN PATINDEX(@pattern, Script COLLATE Latin1_General_CS_AI) ELSE 0 END

              - 90, /* Start showing 90 characters before match, for context */

              LEN(Script)

       )

  FROM TableWithNameAndScriptHere

  WHERE 1 = 0 /* bogus FALSE condition to ease adding or removing "OR" clauses */

       OR (@ignoreCase = 1 AND (script like @pattern COLLATE Latin1_General_CI_AI))

       OR (@ignoreCase = 0 AND (script like @pattern COLLATE Latin1_General_BIN))

order by Name

The "SUBSTRING" part is to show the context where the first match is enclosed. As mentioned above, it's not possible to specify two different COLLATE statements within a CASE, so the solution is to specify each COLLATE in its own CASE.

The correct CASE will result in the index where the pattern first matches, whereas the incorrect CASE will result in 0. The two can then be added up so that their applicability can be interchanged by the @ignoreCase variable.

The WHERE clause itself only needs different LIKE conditions for each COLLATE.

Easy enough.