Write a case sensitive WHERE CLAUSE

Question

I'm trying to fix addresses in a client database. I have a function that takes a string and returns a string where the first letter of every word is capitalized.

I am trying to only pass the addresses that are all caps, or all lowercase, but it's returning the entire dataset instead. The first three columns are all correct, so I can't figure out why the WHERE isn't working.

Here's my code.

    SELECT
        [ConvertFirstLettertoCapital]([Address]) AS [Standard],
        UPPER([Address]) AS [Upper],
        LOWER([Address]) AS [Lower],
        [Name],
        [Address],
        [Address 2],
        [City],
        [State]
    FROM [Address_List]
    WHERE
        [Address] = UPPER([Address]) OR
        [Address] = LOWER([Address])

• SQL Server v15.0.1083.2
• SQL Server Management Studio v15.0.18330

Answer 1

Normally SQL Server is not case sensitive. So 'ABC'='abc' is true in a where clause.

To make a where clause case sensitive, you can use COLLATE. Please try the below query instead:

WHERE
        [Address] = UPPER([Address]) COLLATE SQL_Latin1_General_CP1_CS_AS
        OR
        [Address] = LOWER([Address]) COLLATE SQL_Latin1_General_CP1_CS_AS