user3600663 user3600663 - 3 months ago 7
SQL Question

SQL Logic to return ONLY uppercase if matches Else it should return the lower case

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.

Table1
ID | Name
1 | William
2 | WILLIAM
3 | ROSE
4 | Rose


I tried with the below query somehow its not giving expected result for all the cases.

Select * from Table1
Where
Table1.Name COLLATE Latin1_General_CS_AI =
(CASE WHEN 'William' COLLATE Latin1_General_BIN LIKE '%[a-z]%'
THEN UPPER('William')
ELSE 'William'
END )

Answer

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.