I have a table like below and then have a input parameter 'William'.
So I need to match the input parameter with the Table1.Name with case sensitive. In case, if the Upper case name is not found then need to return the lower case.
ID | Name
1 | William
2 | WILLIAM
3 | ROSE
4 | Rose
Select * from Table1
Table1.Name COLLATE Latin1_General_CS_AI =
(CASE WHEN 'William' COLLATE Latin1_General_BIN LIKE '%[a-z]%'
Assuming that your column currently has a case insensitive collation you can use
SELECT TOP 1 WITH TIES * FROM Table1 WHERE Name = 'William' ORDER BY CASE WHEN Name = UPPER('William') COLLATE Latin1_General_CS_AI THEN 0 ELSE 1 END
As the collation is case insensitive it will bring back all variants of William. Ones matching "WILLIAM" will be scored 0 and ones matching any other variant as 1 and the
TOP 1 WITH TIES retains the group with the lowest score that exists in the results.