To get the equivalent of :

CASE
WHEN 'a' THEN 'Alabama'
WHEN 'A' THEN 'Arizona'
END

in a case insensitive database do the following:

CASE
WHEN char(97) THEN 'Alabama'
WHEN char(65) THEN 'Arizona'
END

-- Case sensitive compare with COLLATE

DECLARE  @string VARCHAR(128); SET @string = 'delta';

SELECT Col1 = @string

WHERE  PATINDEX('delta',@string COLLATE SQL_Latin1_General_CP1_CS_AS) > 0

GO

-- delta

-- (1 row(s) affected)

DECLARE  @string VARCHAR(128); SET @string = 'Delta';

SELECT Col1 = @string

WHERE  PATINDEX('delta',@string COLLATE SQL_Latin1_General_CP1_CS_AS) > 0

GO

-- (0 row(s) affected)

------------