CaseSensitivity in an user-defined function T-SQL

Question

I have an user-defined function in T-SQL which uses CHARINDEX() internally. I take @caseSensitivity(bit) parameter as an input, which should decide the casing(collation) of the CHARINDEX() function. Is there a way to do that without making two if-constructs for every occurence of CHARINDEX()?

Answer 1

Unfortunately, there isn't a way to set a connection-level collation override, if that is what you were thinking (I was).

However, if you are just looking for a way to be able to control this easier, then you may be able to evaluate all the CHARINDEX lookups as case-sensitive and then use UPPER to make it case-insensitive when it @caseSensitive is zero:

DECLARE @caseSensitivity BIT = 1
DECLARE @input VARCHAR(3) = 'Abc' 
DECLARE @inputCheck VARCHAR(3) = 'B'

IF @caseSensitivity = 0
BEGIN
    SET @input = UPPER(@input)
    SET @inputCheck = UPPER(@input)
END

SELECT CHARINDEX(@inputCheck COLLATE SQL_Latin1_General_CP1_CS_AS, @input COLLATE SQL_Latin1_General_CP1_CS_AS, 0)